CODEWITHSUNDEEP
sql-server-2008
Sonam Mohite
Sonam Mohite

Reputation: 903

How to set If condition in Where clause in Sql

I want to display a criteria having multiple conditions, but if any condition is null or not having any value than how to set in where clause. Example:

where
SR.fk_Designation_ID = @pk_Designation_ID
and SR.fk_SeniorityCategory_ID = @pk_SeniorityCategory_ID
and SR.IsCurrent_Appointment = 1 and SR.fk_SeparationType_ID is null
order by Senority_Joining_For_Order,E.Date_Of_Birth

here if @pk_SeniorityCategory_ID is null than I don't want to include the and SR.fk_SeniorityCategory_ID = @pk_SeniorityCategory_ID condition in where clause.

Upvotes: 1

Views: 1477

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

Try this:

WHERE
SR.fk_Designation_ID = @pk_Designation_ID
AND (@pk_SeniorityCategory_ID IS NULL 
     OR 
     SR.fk_SeniorityCategory_ID = @pk_SeniorityCategory_ID)
AND SR.IsCurrent_Appointment = 1 AND SR.fk_SeparationType_ID IS NULL
ORDER BY Senority_Joining_For_Order,E.Date_Of_Birth

Upvotes: 2

Related Questions