How to avoid specification of return parameters in an inline table function
-
Hi guys, I am having an inlined table valued function to search the users in my site. The search is optional ie. sometimes user will search with first name, sometimes he searches with last name, and sometimes both. As of now I am using if-else statements to check whether first name is ''(empty),or last name is ''(empty) or both are ''(empty). I have two questions? 1. Is there any other way, so that I can avoid these if-else statements and make it a single query? 2. If I am using a function as shown below, I have to specify all the return parameters. Is there any way I can avoid this and return the complete table(I do not want to use stored procedures)? I'm using a freetext search,because I want to get the search results according to relevence(ranked results). ALTER FUNCTION [dbo].[Search] ( @FirstName Varchar(400),@LastName Varchar(400) ) RETURNS @SearchResults TABLE ( -- Add the column definitions for the TABLE variable here UserName Varchar(400), EmailAddress Varchar(400) ) AS BEGIN IF (@FirstName<>'' AND @LastName<>'') begin insert into @SearchResults Select Top 100 Percent UserName,EmailAddress From UsersFirst t JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key] JOIN UsersSecond U on t.UserId=U.Id JOIN FreetextTable(UsersSecond, LastName, @LastName) As L On U.Id = L.[Key] ORDER BY K.[Rank] DESC; end ELSE IF (@FirstName<>'') begin insert into @SearchResults Select Top 100 Percent UserName,EmailAddress From UsersFirst t JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key] JOIN UsersSecond U on t.UserId=U.Id ORDER BY K.[Rank] DESC; end return END Thanks In Advance
jophin