none
IF ELSE vs CASE WHEN

    Question

  • Can someone explain why the first statement works, but the second one generates an error if myfunc() does not exist? 

    1) if exists(select name from sysobjects where name = 'myfunc') select dbo.myfunc();else select 0;

    2) select case when exists(select name from sysobjects where name = 'myfunc') then dbo.myfunc() else 0 end;

    Generates this error if myfunc() does not exist, even though it should not be executing that case:

    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myfunc", or the name is ambiguous.

    Thursday, January 16, 2014 7:49 PM

Answers

  • The explanation is this...

    SQL Server will always return the same data type regardless of which branch of the case/when expression is actually returned.  In your case, one branch has a function call so SQL server needs to check the return data type of your user defined function.

    I don't know the actual code that SQL Server uses to determine the return data type of a function, but it is probably similar to this...

    Select	system_type_id
    From	sys.all_parameters As P
    Where	P.object_id = object_id('YourFunctionNameHere')
    		And is_output = 1


    If the function does not exist, SQL cannot determine the return data type, and therefore throws the error.  You should realize that the query engine makes this determination before the code is actually executed (during the compile phase).  Therefore, it does not matter that the particular "when" branch is not executed.

    As pointed out by others, there are ways around this.  I posted this because you wanted an explanation.




    Thursday, January 16, 2014 10:41 PM
  • Neil.W,

    (Just a guess) Both of these statement lies under T-SQL section. "If" is a part of procedural level programming that is enabled in SQL by MSft. Whenever you are using an "If" statement (as you know it is object based not set based), it looks for the given condition to be true or false nothing else. Whereas when you are using a "CASE" statement, it is a set based syntax, thus it'll look for the return type of that function to match with the other replaceable, that is  "0" in your case.

    I suppose you already know that all the data types in a case statement should be same or should be available for implicit conversion by the SQL engine.

    Thus in your problem statement when Engine starts searching the function "myfunc()" for its return type it does not finds anything, that depicts it can never match the data type of "0"(INT) to that of the return type of function.

    Only problem here which I see is that MSft should come with proper error statement in this case. Instead of "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myfunc", or the name is ambiguous."  it should say something like "Implicit conversion failed since function does not exist"


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Neil Weicher Friday, January 17, 2014 11:06 AM
    Friday, January 17, 2014 7:14 AM

All replies

  • For CASE expressions all expressions should be of the same type or implicitly convertible types. Therefore I suspect it attempts to call this function in order to find out the return type.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, January 16, 2014 8:04 PM
    Moderator
  • you might want to add a "select" before that .. as the sql engine expects for a value in that particular position. When you have it as a subquery - the value is provided .. 
    select case when exists(select name from sysobjects where name = 'myfunc') then (select dbo.myfunc()) else 0 end;


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Thursday, January 16, 2014 8:05 PM
  • You have not created any function with a name dbo.myfunc(). Create the function, it will resolve your error.


    create FUNCTION dbo.myfunc()
    RETURNS int 
    AS 
    BEGIN
           RETURN 100
    END;

    select case when exists(select name from sysobjects where name = 'myfunc') then dbo.myfunc() else 0 end;

    -Prashanth


    Thursday, January 16, 2014 8:07 PM
  • You should be able to find the information from the definition. 

    http://msdn.microsoft.com/en-us/library/ms181765.aspx

    You cannot use CASE as a Flow Control method and the CASE eveluates its expression sequentially so it errors in your example.

    "The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. 

    ..

    The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.

    "

    Thursday, January 16, 2014 8:11 PM
    Moderator
  • Thanks - but you are missing the point of the question. Assuming myfunc does not exist, why does the first statement work, but the second statement fails?
    Thursday, January 16, 2014 8:21 PM
  • Right - but the first condition fails so theoretically it should not try to execute the UDF.
    Thursday, January 16, 2014 8:22 PM
  • Number 1 is TRANSACT-SQL, running as a programming layer, and the flow of control determines what statements (including what SQL statements) get sent to the database engine.  In number 1's case, the SQL statement itself (the Select statement) isn't run, but the Transact SQL script is.

    Number 2 is a SQL statement, and even though MyFunc would not be run (in a hypothetical world), it's still part of the SQL statement, and the statement is sent to the database engine to be processed and run.

    Here's an extreme example:

    Declare @X int
    RETURN
    Select dbo.func()  -- This line also is never executed and never errors out, because the TRANSACT SQL script exited.

    It'll make you scratch your head, but it's what is happening.


    • Edited by johnqflorida Thursday, January 16, 2014 8:54 PM Clarified wording
    Thursday, January 16, 2014 8:45 PM
  • The only explanation I can think of that 2 different project teams programmed it during the years.

    There is no T-SQL theoretical explanation for it.

    if exists(select name from sysobjects where name = 'myfunc') select dbo.myfunc()
    else select 0;
    -- 0
    
    select case when exists(select name from sysobjects where name = 'myfunc') then dbo.myfunc() else 0 end;
    /*
    Msg 4121, Level 16, State 1, Line 1
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myfunc", or the name is ambiguous.
    */


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Thursday, January 16, 2014 9:31 PM
    Moderator
  • See this blog post

    SQL Server Case/When Data Type problems

    It explains that even if the code is not supposed to be executed, it still executes.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, January 16, 2014 10:10 PM
    Moderator
  • And to add a bit... It syntax checks, but doesn't execute the SQL, when the interpreter processes the Transact SQL, but an IF statement causes a line or block not to execute.

    Examples:

    if 1 = 2
    	jkljlkljljlkjljlj  /* This will fail, incorrect syntax */
    
    if 1 = 2
    	slelect 1234 /*  This will fail, incorrect syntax ("sLelect, not Select) */
    
    if 1 = 2
    	select 'x' from jkljlkjlkjlkj /*  Completes.  Syntax correct, even if table doesn't exist */
    
    if 1 = 2
    	Select dbo.jkljkljlkjlk() /*  Completes. Syntax correct, even if function doesn't exist */
     


    Thursday, January 16, 2014 10:20 PM
  • The explanation is this...

    SQL Server will always return the same data type regardless of which branch of the case/when expression is actually returned.  In your case, one branch has a function call so SQL server needs to check the return data type of your user defined function.

    I don't know the actual code that SQL Server uses to determine the return data type of a function, but it is probably similar to this...

    Select	system_type_id
    From	sys.all_parameters As P
    Where	P.object_id = object_id('YourFunctionNameHere')
    		And is_output = 1


    If the function does not exist, SQL cannot determine the return data type, and therefore throws the error.  You should realize that the query engine makes this determination before the code is actually executed (during the compile phase).  Therefore, it does not matter that the particular "when" branch is not executed.

    As pointed out by others, there are ways around this.  I posted this because you wanted an explanation.




    Thursday, January 16, 2014 10:41 PM
  • That's a great explanation and article, it's very accurate and true.  I think it's a different topic though, because mixing data types in a Case statement yields the error message (in this case) ""Conversion failed when converting the varchar value 'Data is empty' to data type int.", such as this statement:

    Declare @Data VarChar(20)
    Set @Data = ''
    Select Case When @Data Is NULL Then NULL
                When @Data = ''    Then 'Data is empty'
                When 0=1           Then 1
                End

    The poster got the error "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myfunc", or the name is ambiguous.", which is a different error, and byproduct of how the Transact SQL interpreter does things.  (Also, the same exact error occurs just by running "Select dbo.myfunc()" or by running "Select case when 1 = 1 then dbo.myfunc() else substring(@@servername, 1, 10) end".

    I don't think anybody has found anything so far that specifically documents this behavior, though, so so it may very well be an undocumented feature.  And generally, undocumented features come with a degree of risk, but they're usually interesting and fun too (off topic: check out "god mode" in Windows 7).

    Friday, January 17, 2014 12:28 AM
  • Neil.W,

    (Just a guess) Both of these statement lies under T-SQL section. "If" is a part of procedural level programming that is enabled in SQL by MSft. Whenever you are using an "If" statement (as you know it is object based not set based), it looks for the given condition to be true or false nothing else. Whereas when you are using a "CASE" statement, it is a set based syntax, thus it'll look for the return type of that function to match with the other replaceable, that is  "0" in your case.

    I suppose you already know that all the data types in a case statement should be same or should be available for implicit conversion by the SQL engine.

    Thus in your problem statement when Engine starts searching the function "myfunc()" for its return type it does not finds anything, that depicts it can never match the data type of "0"(INT) to that of the return type of function.

    Only problem here which I see is that MSft should come with proper error statement in this case. Instead of "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myfunc", or the name is ambiguous."  it should say something like "Implicit conversion failed since function does not exist"


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Neil Weicher Friday, January 17, 2014 11:06 AM
    Friday, January 17, 2014 7:14 AM
  • If the function does not exist, SQL cannot determine the return data type, and therefore throws the error.  You should realize that the query engine makes this determination before the code is actually executed (during the compile phase).  Therefore, it does not matter that the particular "when" branch is not executed.

    Hi George,

    That's fine. Nonetheless for the sake of Computer Science programming consistency shouldn't IF...ELSE behave the same way?


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Friday, January 17, 2014 9:53 AM
    Moderator
  • This sounds like the most plausible explanation.
    Friday, January 17, 2014 11:03 AM
  • It's an interesting topic.  There' still more to it, though, and the IF statement definitely causes certain types of errors not to be detected under certain conditions (syntax always has to be valid though).  

    If 1 = 2
    	Select 'z' + 1 + Cast('2012-01-01' as datetime) 
    /* Select doesn't execute, but no error either. 3 different datatypes in one statement */
    
    Select 'z' + 1 + Cast('2012-01-01' as datetime) 
    /* Throws expected "conversion failed" error. */
    
    If 1 = 2
    	Fetch fdsjkdjfslkjsfdljkldsf 
    /* Fetch doesn't execute because of the IF, but no error thrown either, even 
    though cursor doesn't exist at all. Datatype not a factor in this example */ Fetch jkljljkjkljljjk /* Throws expected 'cursor doesn't exist' error. */ Declare @X int If 1 = 2 Select @X = 'sdffds' Else Select @X = 123 /* Executes the ELSE select statement, with no error even though first select is invalid. */ Declare @X int If 1 = 1 Select @X = 'sdffds' Else Select @X = 123 /* Throws the expected 'conversion failed' error. Same if you just select @X = 'sdffds' */

    But here's an odd example (not exactly the same circumstance as the function call, but part of that same general family) that shows datatypes certainly come into play too.  The first example doesn't throw an error if the Case flow doesn't execute the first expression (even though it's invalid), but the second (identical except it causes the first expression to execute) does.

    Select Case when 1 = 2 then  'z' + 1 + @@PROCID + Current_Timestamp Else  41030 end	 
    /* Throws no error, and interprets the 41030 as a datevalue and returns a date */
    
    Select Case when 1 = 1 then  'z' + 1 + @@PROCID + Current_Timestamp Else  41030 end
    /* But if the case evaluates to execute the first erroneous formula, the expected conversion failed error occurs */



    Friday, January 17, 2014 1:53 PM
  • Kalman,

    Think of it this way....  There are compile time errors and there are run-time errors.  Syntactically, there is nothing wrong with:

    if exists(select name from sysobjects where name = 'myfunc') 
        select dbo.myfunc();
    else 
        select 0;

    The first paragraph of this page (Deferred Name Resolution) is: 

    When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the sys.sql_modules catalog view.

    The Case/when syntax fails the syntax check because the function does not exist and therefore the return type cannot be determined.

    By the way, imagine a world without deferred name resolution.  In such a world, we would need to make sure the stored procedures are created in a particular order.  For example, if SP_1 calls SP_A and SP_B then would would need to create SP_A and SP_B first and then create SP_1.  This would make scripting stored procedures infinitely more difficult.


    Friday, January 17, 2014 6:37 PM
  • Hi George,

    But this is not a stored procedure.  They are just 2 statements: IF...ELSE construct and SELECT statement with CASE expression.

    No matter how you slice it, logically they should be consistent. But they are not.

    As far as stored procedure should require that a function exists at compile time, that would be a good thing. We should not cater to sloppy programming habits.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Friday, January 17, 2014 9:26 PM
    Moderator