Search code examples
sqlsql-serversql-server-2005

An expression of non-boolean type specified in a context where a condition is expected, near 'END'


So maybe someone can point me in the right direction of what is causing this error? I've been fighting with this for a couple of hours and searching the web, and I can't figure out what I'm doing wrong here. It's included as part of a stored procedure, I don't know if that matters, if it does I can include that as well. Tables and field names have been changed to protect the innocent... meaning my job. Thanks.

SELECT
              /* The fields are here*/
FROM
              /* my joins are here */
WHERE
    (Table.Field = stuff)
    AND
    (Table.Field2 = otherstuff)
    AND
    (Table2.Field3 = someotherstuff)
    AND
    CASE @param1
        WHEN 0 THEN 'Table.Field IS NULL'
        WHEN 1 THEN 'Table.Field2 IS NOT NULL'
        ELSE ''
    END

Thanks for the responses. Technically egrunin was the correct answer for this question, but OMG Ponies and Mark Byers were pretty much the same thing just missing that last piece. Thanks again.


Solution

  • I'm pretty sure the other answers leave out a case:

    WHERE 
    (Table.Field = stuff)
    AND
    (Table.Field2 = otherstuff)
    AND
    (Table2.Field3 = someotherstuff)
    AND
    (
        (@param1 = 0 and Table.Field IS NULL)
        OR
        (@param1 = 1 and NOT Table.Field2 IS NULL)
        OR
        (@param1 <> 0 AND @param1 <> 1) -- isn't this needed?
    )