if condition in where clause of sql query

Question

Below is my where clause of the query when i want data for a specific user

where Completion_Date>= '11/01/2011' 
and Completion_Date<= '12/11/2012' 
and System_user_id = 1234

and below is the where clause when i want to pull data for all the user:

where Completion_Date>= '11/01/2011' 
and Completion_Date<= '12/11/2012'

Since i dont want 2 seperate queries, is there a way to add a condition in where clause so that i can use a single query and depending on the input (i.e System_user_id) it will decide on whether to add extra condtion in the query. I will be sending -1 when i want data for all users & for a specific user its system_user_id will be sent.

Answer 1

You may try the following procedure.

Updated Query

declare @userid int = -1
if (@userid = -1)
    BEGIN

     SELECT * FROM mytable 
     where Completion_Date>= '11/01/2011' 
     and Completion_Date<= '12/11/2012' 
     and userid in 
         (select distinct userID from mytable)
    end
ELSE
    BEGIN

     SELECT * FROM mytable 
     where Completion_Date>= '11/01/2011' 
     and Completion_Date<= '12/11/2012' 
     and userid = @userid 

end;

Results:

USERID  NAME    COMPLETION_DATE
123     john    2011-11-01
125     tim     2011-11-02
127     ron     2011-11-08

• REference SQLFIDDLE

To see a specific user:

• Reference SQLFIDDLE

---

ANOTHER METHOD

Updated after latest comment from OP

• SQLFIDDLE

Query:

DECLARE @uid int = -1
SELECT 
*
FROM mytable 
WHERE 
( CASE 
     WHEN @uid <> -1 THEN @uid
     ELSE userid
  END
) = userid

and Completion_Date>= '11/01/2011' 
     and Completion_Date<= '12/11/2012' 
;

Results: when @uid = -1

USERID  NAME    COMPLETION_DATE
123     john    2011-11-01
125     tim     2011-11-02
127     ron     2011-11-08

Please comment if you have tried this out :)

Answer 2

Try:

WHERE ((@System_user_id = -1)
  AND (Completion_Date >= '11/01/2011') 
  AND (Completion_Date <= '12/11/2012'))
OR ((@System_user_id <> -1) 
  AND (System_user_id = @System_user_id) 
  AND (Completion_Date >= '11/01/2011') 
  AND (Completion_Date <= '12/11/2012'))

A variation on this using a Common Table Expression (SQL Fiddle)

;WITH CompletionDates AS
(
    SELECT *
    FROM MyTable
    WHERE Completion_Date >= '11/01/2011'
    AND Completion_Date <= '12/11/2012'
)
SELECT * 
FROM CompletionDates
WHERE (@System_user_id = -1) OR (System_user_id = @System_user_id)