Saturday, June 6, 2015

Case in where clause in SQL Server


Old Approach (will be messy in case of multiple columns in where clause)

If @Id = 0
    Select * From tbl
Else
    Select * From tbl Where Id = @Id




Good Approches:

-- Do the comparison, OR'd with a check on the @Country=0 case
Select * From tbl WHERE (Id = @Id OR @Id = 0)

-- compare the Country field to itself
Select * From tbl WHERE Id = CASE WHEN @Id > 0 THEN @Id ELSE Id END