An expression of a non-boolean type specified in the context where the condition is expected, next to "END"

Question

So maybe someone can point me in the right direction of what causes this error? I struggled with this for several hours and searched the Internet, and I cannot understand what I am doing wrong here. It is included as part of the stored procedure, I do not know if it matters, if so, I can include this as well. The tables and field names have been changed to protect the innocent ... which means my job. Thank.

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 answers. Technically, egrunin was the correct answer to this question, but OMG Ponies and Mark Byers were pretty much the same as without this last part. Thanks again.

Answer 1

, :

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

Answer 2

case, . . , , SQL, :

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

Answer 3

• CASE WHERE,
• The text you provide in CASE will only be executed if you used dynamic SQL

Using:

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 does not make sense if you already have Table.Field = stuff, etc.

Parameters that would be better executed — either make the entire query dynamic SQL, or just one parameter — use the IF / ELSE statement with the individual queries and the correct WHERE clauses.