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
  2. FrankKalis Moderator

  3. Adriaan New Member

    Huh? I would call CASE a function: it processes a number of parameters, and returns a value.
  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
  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.
  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>
  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>
  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
  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>
  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
  11. FrankKalis Moderator

  12. Adriaan New Member

    Frank, hair splitting is one of the more serious problems in the world today.
  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>
  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>

Share This Page