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.

Posted

in

,

by

Tags:


Related Posts

Comments

3 responses to “Case statement in Where Clause – SQL Server – Conditional Where clause”

  1. karthik Avatar

    it worked thanks for the information

  2. Phani Avatar
    Phani

    Thanks for the info, Its working…. 🙂

  3. nithin Avatar
    nithin

    i want write where clause

    when Address line = address line two then ‘Y’
    when name = address_line_one then ‘Y’

    How can i fetch this in case statement.

Leave a Reply to nithinCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading