No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter). Let’s try to figure out the problem and its solution with a simple scenario.
Suppose we have a table to keep students result with follow structure
CREATE TABLE #Result
(
StudentId INT,
TeacherId INT,
GroupId INT,
Result VARCHAR(10),
MarksObtained INT
)
GO
INSERT INTO #Result
SELECT 101,1,1,'PASS',510 UNION ALL
SELECT 102,1,1,'PASS',622 UNION ALL
SELECT 103,2,1,'FAIL',174 UNION ALL
SELECT 104,2,2,'PASS',652 UNION ALL
SELECT 105,3,2,'FAIL',134
Our requirement is to create a stored procedure with only two parameters, one for id (it could be student, teacher or group id), we will call it @id and other to hold information that will decide that what type of id is being passed to stored procedure i.e. student, teacher or group, we will call it @idType
DECLARE @Id INT -- It could be StudentId,TeacherId,GroupId
DECLARE @IdType VARCHAR(10) -- Type could be Student,Teacher or Group
SELECT @Id = 2, @IdType= 'Teacher'
--OR-- @Id = 102, @IdType= 'Student'
--OR-- @Id = 1, @IdType= 'Group'
Let’s move to our targeted query, with conditional where clause.
SELECT * FROM #Result
WHERE 1 = (CASE
WHEN @IdType='Student' AND StudentId = @Id
THEN 1
Read more: Connect SQL