LemonC200 LemonC200 - 2 months ago 9
SQL Question

Use declare to exclude or include parts of queries

I was just wondering if it was possible to exclude or include parts of a query.
By declaring a variable as yes or no. This would make things much easier then declaring every value in for example the 'IN' clause.

Example stated below

Startingpoint

SELECT
E.Id, E.Name
FROM
Employees AS E
WHERE
E.Email LIKE '%@company.com'
AND E.Id NOT IN ('1001,1002,1009,1115,2000')
AND E.Team = 0


Ideal situation

DECLARE @Includemanagers nvarchar (255) = 'No'

SELECT
E.Id, E.Name
FROM
Employees AS E
WHERE
E.Email LIKE '%@company.com'
AND CASE
WHEN @Includemanagers = 'No'
THEN E.Id NOT IN ('1001,1002,1009,1115,2000')
END
AND E.Team = 0


I know the syntax is not correct. And I doubt if this is possible.


For reference in the future two correct answers are given.

Use answer from @Jamiec when you DO NOT want to include PRINT statement

Use answer from @Prdp when you want to include print statement

Answer

This is a simple OR condition

DECLARE @Includemanagers nvarchar (255) = 'No'

SELECT E.Id, E.Name
FROM Employees AS E
WHERE E.Email LIKE '%@company.com'
AND (@IncludeManagers = 'yes' 
     OR (@IncludeManagers = 'no' AND E.Id NOT IN (1001,1002,1009,1115,2000 ))
AND E.Team = 0

(Note that your IN clause was quoted, and that wont work. Corrected in this answer)

Comments