Help! - SQL - IF Else IF logic for returning Containstable selection

Question

Hi guys, I'm trying to use IF else If logic inside an inline table valued function for SQL and returning a containstable based on that logic. but i'm having syntax problems with the IF Else IF block. thanks for the help. since i can't parametrize the columns in the containstable i have to resort to using if else statements. here's the code. thanks.

i'm getting

Msg 156, Level 15, State 1, Procedure FullTextSearch, Line 17 Incorrect syntax near the keyword 'IF'.

ALTER FUNCTION [dbo].[FullTextSearch] 
(    
    @Columns nvarchar(100), @SearchPhrase nvarchar(100)
)

RETURNS TABLE 
AS    
RETURN 


IF (@Columns='Title')
   BEGIN
   SELECT * 
     from projects as P inner join
     containstable(PROJECTS, Title, @SearchPhrase) as K 
     on P.project_id = K.[KEY]
   END

ELSE IF (@Columns='Project_Details')
   BEGIN
   SELECT * 
 from projects as P inner join
 containstable(PROJECTS, Project_Details, @SearchPhrase) as K 
 on P.project_id = K.[KEY]
   END

ELSE IF (@Columns='Contact_Info')
   BEGIN
   SELECT * 
 from projects as P inner join
 containstable(PROJECTS, Contact_Info, @SearchPhrase) as K 
 on P.project_id = K.[KEY]
   END

ELSE IF (@Columns='Project_Description')
   BEGIN
   SELECT * 
     from projects as P inner join
     containstable(PROJECTS, Project_Description, @SearchPhrase) as K 
     on P.project_id = K.[KEY]
   END

ELSE -- (@Columns='All')
   BEGIN
   SELECT * 
     from projects as P inner join
     containstable(PROJECTS, (Title, Project_Details, Contact_Info,     Project_Description), @SearchPhrase) as K 
     on P.project_id = K.[KEY]
   END

Answer 1

First of all, you have == on the first IF instead of =

Oh yeah and I noticed that within the CONTAINSTABLE parameters in your ELSE you have ProjectDetails listed twice

If you are searching a specific field could you just use this syntax:

WHERE Title LIKE '%'+ @Search +'%'
      OR ProjectDetails LIKE '%' + @Search + '%'
      OR ContactInfo LIKE '%' + @Search + '%')

(Warning) This syntax ignores any indexing.

You can check this blog for a solution that works with indexing. It seems to have a good solution.

Answer 2

Look at the multi-statement function template of the sql management studio i.e. on menu: View->Template Explorer-Create Multi-Statement Table-valued Function.

The issue is the syntax is different for the multi statement function. Also note that as jmein said there is a == on the first if.

Answer 3

You're getting this error because you're trying to use multiple statements in an inline table-valued function.

An inline table-valued function must contain a single SELECT statement, something along the lines of:

CREATE FUNCTION dbo.MyInlineFunction (@MyParam INT)
RETURNS TABLE
AS
RETURN 
(
    SELECT first_column, second_column FROM ...
)

Your function needs to be declared as a multi-statement function, syntax similar to this:

CREATE FUNCTION dbo.MyMultiStatementFunction (@MyParam INT)
RETURNS @MyTable TABLE 
(
    first_column INT, second_column VARCHAR(100)  -- etc
)
AS
BEGIN
    IF (@MyParam = 1)
        INSERT INTO @MyTable (...)
        SELECT ... FROM ...
    ELSE
        INSERT INTO @MyTable (...)
        SELECT ... FROM ...

    RETURN
END