[sql] If Else If In a Sql Server Function

I have this function I am trying to create. When I parse it, it works fine, but to actually create the function in the database it says my column names are invalid. That is not true, I spelled them correctly. Here is the code:

ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS int

AS 

BEGIN 

DECLARE @Final nvarchar
IF EXISTS (

    SELECT 
        question, 
        yes_ans, 
        no_ans, 
        na_ans, 
        blank_ans 
    FROM dbo.qrc_maintally 
    WHERE school_id = @SchoolId 

)

    IF yes_ans > no_ans AND yes_ans > na_ans 
    BEGIN
        SET @Final = 'Yes'
    END

    ELSE IF no_ans > yes_ans AND no_ans > na_ans 
    BEGIN
        SET @Final = 'No'
    END

    ELSE IF na_ans > yes_ans AND na_ans > no_ans 
    BEGIN
        SET @Final = 'N/A'
    END

RETURN @Final

END

I think you'd be better off with a CASE statement, which works a lot more like IF/ELSEIF

DECLARE @this int, @value varchar(10)
SET @this = 200
SET @value = (
SELECT 
CASE
    WHEN @this between 5 and 10 THEN 'foo'
    WHEN @this between 10 and 15 THEN 'bar'
    WHEN @this < 0 THEN 'barfoo'
    ELSE 'foofoo'
    END
)

More info: http://technet.microsoft.com/en-us/library/ms181765.aspx

ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
    RETURNS nvarchar(3)
AS BEGIN 

    DECLARE @Final nvarchar(3)
    SELECT @Final = CASE 
        WHEN yes_ans > no_ans  AND yes_ans > na_ans THEN 'Yes'
        WHEN no_ans  > yes_ans AND no_ans  > na_ans THEN 'No'
        WHEN na_ans  > yes_ans AND na_ans  > no_ans THEN 'N/A' END
    FROM dbo.qrc_maintally
    WHERE school_id = @SchoolId

Return @Final
End

As you can see, this simplifies the code a lot. It also makes other errors in your code more obvious: you're returning an nvarchar, but declared the function to return an int (corrected in the code above).

You'll need to create local variables for those columns, assign them during the select and use them for your conditional tests.

declare @yes_ans int,
        @no_ans int,
        @na_ans int

SELECT @yes_ans = yes_ans, @no_ans = no_ans, @na_ans = na_ans 
    from dbo.qrc_maintally 
    where school_id = @SchoolId

If @yes_ans > @no_ans and @yes_ans > @na_ans 
begin
Set @Final = 'Yes'
end
-- etc.

No one seems to have picked that if (yes=no)>na or (no=na)>yes or (na=yes)>no, you get NULL as the result. Don't believe this is what you are after.

Here's also a more condensed form of the function, which works even if any of yes, no or na_ans is NULL.

USE [***]
GO
/****** Object:  UserDefinedFunction [dbo].[fnActionSq]    Script Date: 02/17/2011 10:21:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS nvarchar(3)
AS 
BEGIN
return (select (
       select top 1 Result from
       (select 'Yes' Result, yes_ans union all
        select 'No', no_ans union all
        select 'N/A', na_ans) [ ]
        order by yes_ans desc, Result desc)
       from dbo.qrc_maintally
       where school_id = @SchoolId)
End

Look at these lines:

If yes_ans > no_ans and yes_ans > na_ans

and similar. To what do "yes_ans" etc. refer? You're not using these in the context of a query; the "if exists" condition doesn't extend to the column names you're using inside.

Consider assigning those values to variables you can then use for your conditional flow below. Thus,

if exists (some record)
begin
   set @var = column, @var2 = column2, ...

   if (@var1 > @var2)
      -- do something

end

The return type is also mismatched with the declaration. It would help a lot if you indented, used ANSI-standard punctuation (terminate statements with semicolons), and left out superfluous begin/end - you don't need these for single-statement lines executed as the result of a test.

If yes_ans > no_ans and yes_ans > na_ans  

You're using column names in a statement (outside of a query). If you want variables, you must declare and assign them.