Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 371
Mike Keller Avatar asked Dec 23 '22 02:12

Mike Keller


2 Answers

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?
)
like image 103
egrunin Avatar answered Dec 24 '22 17:12

egrunin


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)
)
like image 36
Mark Byers Avatar answered Dec 24 '22 16:12

Mark Byers