SQL Where clause based on Status-CodePudding

Declare @Status nvarchar(25) = Null

Select * From Products
Where Type = 1
And
If Status is null, Don't add clause(means return all)
If Status = 'C', Add Status = 'Completed'
If Status = 'U', Add Status = 'Uncompleted'

How can I do this?

CodePudding user response：

Don't call your variable the same as your column

Declare @MyStatus nvarchar(25) = Null

Select * From Products
Where Type = 1
AND (
  (MyStatus is null)
  OR (MyStatus = 'C' AND Status = 'Completed')
  OR (MyStatus = 'U' AND Status = 'Uncompleted')
)

CodePudding user response：

It would be better to simply assign the desired value to your @Status variable then you can simply check for equality -

where Type = 1 and (@status = status or @Status is null);