Case statement in Where Clause – SQL Server – Conditional Where clause

In few scenario, we might need to perform conditional where clause. Some time the condition can be written well in OR with Where clause but in some scenario it is not possible.

so this time, i have come up with one more article on writing the conditional where clause.

Example:

DECLARE @Student Table (Stream Varchar(20), [Name] Varchar(20), isAllow BIT )

INSERT Into @Student
VALUES
('Computer Engineering','Minal Zaa', 0)

INSERT Into @Student
VALUES
('Civil Engineering','Manoranjan Sahoo', 1)

INSERT Into @Student
VALUES
('scientist','Santosh Karemore', 1)

INSERT Into @Student
VALUES
('BCom','Rahul jha', 0)

SELECT
   Stream,
   [Name]
FROM
   @Student
WHERE
CASE
    WHEN Stream = 'Computer Engineering' THEN 1
    WHEN isAllow = 1 THEN 1
END = 1

Output:

Case statement in Where Clause

In above code, we have checked that if stream is ‘Computer Engineering’ then display the record or if IsAllow bit is true then display the record. Same query can be written using OR operator but here i wanted to demonstrate the use of Case statement in Where Clause.

  • http://www.techtorule.com karthik

    it worked thanks for the information

  • Phani

    Thanks for the info, Its working…. :)