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

Question

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.

Answer 1

You are returning a string from your case expression, but only a boolean can be used. The string is not evaluated. You could do what you want using dynamic SQL, or you could write it like this instead:

AND (
    (@param1 = 0 AND Table.Field IS NULL) OR
    (@param1 = 1 AND Table.Field IS NOT NULL)
)

Answer 2

1. You can't use CASE in the WHERE clause like you are attempting
2. The text you provided in the CASE would only run if you were using dynamic SQL

Use:

WHERE Table.Field = stuff
  AND Table.Field2 = otherstuff
  AND Table2.Field3 = someotherstuff
  AND (   (@param1 = 0 AND table.field IS NULL)
       OR (@param1 = 1 AND table.field2 IS NOT NULL))

...which doesn't make sense if you already have Table.Field = stuff, etc...

Options that would perform better would be to either make the entire query dynamic SQL, or if there's only one parameter - use an IF/ELSE statement with separate queries & the correct WHERE clauses.

Answer 3

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?
)