SQL Server Performance

CASE

Discussion in 'The Lighter Side of Being a DBA' started by Madhivanan, Jun 18, 2007.

  1. Madhivanan Moderator

    BOL says that CASE is a function
    But nowhere I see people specifying that as a function
    Instead it is widely known as statement or expression

    Am I missing anything?

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Jun 18, 2007
    #2

  2. FrankKalis Moderator

    FrankKalis, Jun 18, 2007
    #2

  3. Adriaan New Member

    Huh? I would call CASE a function: it processes a number of parameters, and returns a value.
    Adriaan, Jun 18, 2007
    #2

  4. FrankKalis Moderator

    From the SQL 92 standard:


    6.9 <case expression>

    Function

    Specify a conditional value.

    Format

    <case expression> ::=
    <case abbreviation>
    | <case specification>

    <case abbreviation> ::=
    NULLIF <left paren> <value expression> <comma>
    <value expression> <right paren>
    | COALESCE <left paren> <value expression>
    { <comma> <value expression> }... <right paren>

    <case specification> ::=
    <simple case>
    | <searched case>

    <simple case> ::=
    CASE <case operand>
    <simple when clause>...
    [ <else clause> ]
    END

    <searched case> ::=
    CASE
    <searched when clause>...
    [ <else clause> ]
    END

    It processes a number of expressions and returns a value expression of the same type.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
    FrankKalis, Jun 18, 2007
    #2

  5. Adriaan New Member

    Weird. Do they call it an expression just because it's in the SQL 92 standard? Otherwise you might as well call a UDF a UDE.
    Adriaan, Jun 18, 2007
    #2

  6. FrankKalis Moderator

    Actually I think it is some sort of hair splitting and there are more serious problems to be solved in the world as the question if it is a function or an expression. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Jun 18, 2007
    #2

  7. satya Moderator

    CASE is a function, I believe it is the expression by the people stating it as a statement [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br />CASE has two formats: <br />The simple CASE function compares an expression to a set of simple expressions to determine the result. <br /><br />The searched CASE function evaluates a set of Boolean expressions to determine the result.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, Jun 18, 2007
    #2

  8. Madhivanan Moderator

    Thank you all [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
    Madhivanan, Jun 18, 2007
    #2

  9. satya Moderator

    Don't you think this is better to move to Lighter side.. section [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, Jun 18, 2007
    #2

  10. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Don't you think this is better to move to Lighter side.. section [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />As you wish [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
    Madhivanan, Jun 18, 2007
    #2

  11. FrankKalis Moderator

    FrankKalis, Jun 18, 2007
    #2

  12. Adriaan New Member

    Frank, hair splitting is one of the more serious problems in the world today.
    Adriaan, Jun 18, 2007
    #2

  13. FrankKalis Moderator

    Absolutely correct! Unfortunately...[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Jun 18, 2007
    #2

  14. satya Moderator

    As we are not discussing about the feature, discussing about what it is called which is out of TSQL perspective. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Why "lighter side"?<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
    satya, Jun 18, 2007
    #2

Share This Page