How to Add a certain condition in where clause on the basis of some value

Question

For example:

SELECT * 
FROM Employee 
WHERE emp.id = @ID 
  AND emp.Name = @Name
  AND emp.isdeleted = @deleted

I want this last condition to be added on certain condition, I don't want to add the condition for emp.isDeleted until or unless @deleted value is passed into the SQL procedure.

If @deleted is null, I don't want AND emp.isdeleted = @deleted condition added to the query.

If @deleted is not null, it means it may have the either 1 or 0 then only I want AND emp.isdeleted = @deleted condition to added in the above SQL query.

Can someone please help me in this if this can be solved apart from dynamic query. since this is just a sample query to make the point but actual one is big enough to convert into dynamic query.

Answer 1

You cannot dynamically add or not add a particular condition to your WHERE clause; but you can be smart about how to specify that condition to handle both your cases.

Try this:

SELECT * 
FROM Employee 
WHERE emp.id = @ID 
  AND emp.Name = @Name
  AND (@deleted IS NULL OR emp.isdeleted = @deleted)