If Else If In a Sql Server Function

Question

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  gt  no ans AND yes ans  gt  na ans      BEGIN         SET  Final    Yes      END      ELSE IF no ans  gt  yes ans AND no ans  gt  na ans      BEGIN         SET  Final    No      END      ELSE IF na ans  gt  yes ans AND na ans  gt  no ans      BEGIN         SET  Final    N A      END  RETURN  Final  END

User · Answer

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  gt   no ans and  yes ans  gt   na ans  begin Set  Final    Yes  end -- etc

User · Answer

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  gt   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

User · Answer

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

User · Answer

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  lt  0 THEN  barfoo      ELSE  foofoo      END     More info  http   technet microsoft com en-us library ms181765 aspx

User · Answer

ALTER FUNCTION  dbo   fnTally    SchoolId nvarchar 50       RETURNS nvarchar 3  AS BEGIN       DECLARE  Final nvarchar 3      SELECT  Final   CASE          WHEN yes ans  gt  no ans  AND yes ans  gt  na ans THEN  Yes          WHEN no ans   gt  yes ans AND no ans   gt  na ans THEN  No          WHEN na ans   gt  yes ans AND na ans   gt  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

User · Answer

If yes ans  gt  no ans and yes ans  gt  na ans     You re using column names in a statement  outside of a query    If you want variables  you must declare and assign them

[sql] If Else If In a Sql Server Function

Examples related to sql

Examples related to sql-server-2005