Professional Web Applications Themes

Case with Nested In Select Not Working - Microsoft SQL / MS SQL Server

Having trouble with a (not so?) simple select-case statement. The stored proc should a datetime value and an operator and retrieve datetimes based on that operator. The error I get is Server: Msg 156, Level 15, State 1, Procedure #stuffproc, Line 19 Incorrect syntax near the keyword 'select'. alter proc #stuffproc ( i_DateOperator varchar(2) , i_DateWanted datetime ) as select lastname , birthdate from Northwind.dbo.Employees where lastname in ( case when i_DateOperator = '==' then select lastname from Northwind.dbo.Employees where birthdate == i_DateWanted when i_DateOperator = '>=' then select lastname from Northwind.dbo.Employees where birthdate >= i_DateWanted when i_DateOperator = '<=' ...

  1. #1

    Default Case with Nested In Select Not Working


    Having trouble with a (not so?) simple select-case statement.

    The stored proc should a datetime value and an operator and retrieve
    datetimes based
    on that operator.

    The error I get is

    Server: Msg 156, Level 15, State 1, Procedure #stuffproc, Line 19
    Incorrect syntax near the keyword 'select'.




    alter proc #stuffproc
    (
    i_DateOperator varchar(2) ,
    i_DateWanted datetime
    )
    as
    select lastname , birthdate
    from Northwind.dbo.Employees
    where lastname in
    (
    case
    when i_DateOperator = '==' then
    select lastname from Northwind.dbo.Employees where birthdate ==
    i_DateWanted
    when i_DateOperator = '>=' then
    select lastname from Northwind.dbo.Employees where birthdate >=
    i_DateWanted
    when i_DateOperator = '<=' then
    select lastname from Northwind.dbo.Employees where birthdate <=
    i_DateWanted
    end
    )
    go

    #stuffproc '==' , '1955-02-08'



    Help!

    Thanks.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    sc Guest

  2. #2

    Default Re: Case with Nested In Select Not Working

    Try this, The Case Expression can't do what you want like you are trying,
    but you can try an IF Statement if you wish. I re-wrote your query using ors

    alter proc #stuffproc
    (
    i_DateOperator varchar(2) ,
    i_DateWanted datetime
    )
    as
    select lastname , birthdate
    from Northwind.dbo.Employees
    where ( i_DateOperator = '==' AND birthdate = i_DateWanted ) OR
    ( i_DateOperator = '>=' AND birthdate >= i_DateWanted ) OR
    ( i_DateOperator = '<=' AND birthdate <= i_DateWanted )
    go

    /*

    #stuffproc '==' , '1955-02-08'
    #stuffproc '==' , '1952-02-19'
    #stuffproc '>=' , '1952-02-19'
    #stuffproc '<=' , '1952-02-19'

    */


    "sc" <net> wrote in message
    news:phx.gbl... 



    Tim Guest

  3. #3

    Default Re: Case with Nested In Select Not Working

    sc,

    You are treating case as a flow of control statement, as other programming
    languages do. For T-SQL it is more a control of data statement.

    Passing an operator as you are doing, you can either use dynamic sql,
    which will work, but has some security and maintainability drawbacks.
    Or seeing there are only 3 possible conditions, just use IF and 3 separate queries.

    if i_DateOperator = '=='
    select lastname , birthdate
    from Northwind.dbo.Employees
    where birthdate = i_DateWanted

    if i_DateOperator = '>='
    select lastname , birthdate
    from Northwind.dbo.Employees
    where birthdate >= i_DateWanted

    if i_DateOperator = '<='
    select lastname , birthdate
    from Northwind.dbo.Employees
    where birthdate <= i_DateWanted

    You could write it with case or union, but it would only obfuscate things, and slow it down.

    Regards
    AJ

    "sc" <net> wrote in message news:phx.gbl... 


    Andrew Guest

  4. #4

    Default Re: Case with Nested In Select Not Working


    Where did you get the idea that "==" is the SQL equality operator?
    That's C, not SQL.

    CREATE PROCEDURE FindBirthdate
    (dateoperator CHAR(2),
    datewanted DATETIME)
    AS
    SELECT lastname, birthdate
    FROM NORTHWIND.DBO.Employees
    WHERE CASE WHEN dateoperator = '= '
    AND birthdate = datewanted
    THEN 1
    WHEN dateoperator = '>='
    AND birthdate >= datewanted
    THEN 1
    WHEN dateoperator = '<='
    AND birthdate <= datewanted
    THEN 1
    ELSE 0 END = 1;

    The CASE expression is an *expresion* and not a control statement; that
    is, it returns a value of one datatype. SQL-92 stole the idea and the
    syntax from the ADA programming language. Here is the BNF for a <case
    specification>:

    <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

    <simple when clause> ::= WHEN <when operand> THEN <result>

    <searched when clause> ::= WHEN <search condition> THEN <result>

    <else clause> ::= ELSE <result>

    <case operand> ::= <value expression>

    <when operand> ::= <value expression>

    <result> ::= <result expression> | NULL

    <result expression> ::= <value expression>

    The searched CASE expression is probably the most used version of the
    expression. The WHEN ... THEN ... clauses are executed in left to right
    order. The first WHEN clause that tests TRUE returns the value given in
    its THEN clause. And, yes, you can nest CASE expressions inside each
    other. If no explicit ELSE clause is given for the CASE expression,
    then the database will insert a default ELSE NULL clause. If you want
    to return a NULL in a THEN clause, then you must use a CAST (NULL AS
    <datatype>) expression. I recommend always giving the ELSE clause, so
    that you can change it later when you find something explicit to return.
    The <simple case expression> is defined as a searched CASE expression in
    which all the WHEN clauses are made into equality comparisons against
    the <case operand>. For example

    CASE iso__code
    WHEN 0 THEN 'Unknown'
    WHEN 1 THEN 'Male'
    WHEN 2 THEN 'Female'
    WHEN 9 THEN 'N/A'
    ELSE NULL END

    could also be written as:

    CASE
    WHEN iso__code = 0 THEN 'Unknown'
    WHEN iso__code = 1 THEN 'Male'
    WHEN iso__code = 2 THEN 'Female'
    WHEN iso__code = 9 THEN 'N/A'
    ELSE NULL END

    There is a gimmick in this definition, however. The expression

    CASE foo
    WHEN 1 THEN 'bar'
    WHEN NULL THEN 'no bar'
    END

    becomes

    CASE WHEN foo = 1 THEN 'bar'
    WHEN foo = NULL THEN 'no_bar' -- error!
    ELSE NULL END

    The second WHEN clause is always UNKNOWN.

    The SQL-92 Standard defines other functions in terms of the CASE
    expression, which makes the language a bit more compact and easier to
    implement. For example, the COALESCE () function can be defined for one
    or two expressions by

    1) COALESCE (<value exp #1>) is equivalent to (<value exp #1>)

    2) COALESCE (<value exp #1>, <value exp #2>) is equivalent to

    CASE WHEN <value exp #1> IS NOT NULL
    THEN <value exp #1>
    ELSE <value exp #2> END

    then we can recursively define it for (n) expressions, where (n >= 3),
    in the list by

    COALESCE (<value exp #1>, <value exp #2>, . . ., n), as equivalent to:

    CASE WHEN <value exp #1> IS NOT NULL
    THEN <value exp #1>
    ELSE COALESCE (<value exp #2>, . . ., n)
    END

    Likewise, NULLIF (<value exp #1>, <value exp #2>) is equivalent to:

    CASE WHEN <value exp #1> = <value exp #2>
    THEN NULL
    ELSE <value exp #1> END

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. sql select case statement
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 9th, 01:50 PM
  2. Select Case SQL recordset help
    By -D- in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 22nd, 10:22 PM
  3. Replies: 0
    Last Post: September 24th, 03:24 AM
  4. Replies: 0
    Last Post: September 11th, 11:26 AM
  5. Replies: 0
    Last Post: September 11th, 12:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139