whisk whisk - 2 months ago 8
SQL Question

CASE expression in WHERE clause selecting rows that are not NULL

I'm trying to write a query that when passed two variables, the first set is numbers 1-4 and the second set of number is 1-2. I want to select only the rows that meet the conditions. I've come up with this so far and am now a little lost on how to call the rows with my results.

DECLARE @Task INT
DECLARE @Status INT

SET @Task = 1 --Test Values can be 1,2,3,4
SET @Status = 1 -- Test Values can be 1,2

SELECT
Pics
,Title
,Photos
,Reports
FROM
DATES
WHERE
CASE
WHEN @Task = 1 AND @Status = 1 THEN Pics IS NOT NULL
WHEN @Task = 2 AND @Status = 1 THEN Title IS NOT NULL
WHEN @Task = 3 AND @Status = 1 THEN Photos IS NOT NULL
WHEN @Task = 4 AND @Status = 1 THEN Reports IS NOT NULL
ELSE NULL
END


Sample data: Here is what i am using this for.

I have 2 drop down boxes, one called Tasks that hold the 4 values Pics, report, photos, title.

Second box holds the complete or not completed 1 or 2

I show all 4 Reports, titles, pics, photos when this query is called, but if i want to see all the reports that are done i can select the report value and then select the complete tab. example (Task)Report = 3 and (Status)complete = 1.

giving me all the reports that are done and still displaying the other 3 values even if they are null.

Answer

What you want is the eternally simple AND/OR:

DECLARE @Task INT 
DECLARE @Status INT 

SET @Task = 1 
SET @Status = 1

SELECT  
     Pics
    ,Title
    ,Photos
    ,Reports

FROM    DATES

WHERE @Status = 1
AND (
        (@Task = 1 AND Pics IS NOT NULL)
    OR
        (@Task = 2 AND Title IS NOT NULL)
    OR
        (@Task = 3 AND Photos IS NOT NULL)
    OR
        (@Task = 4 AND Reports IS NOT NULL) 
    )