IF statement in SQL Server where clause

问题:

I am having a nightmare trying to come up with something which will achieve the following in SQL Server 2008.

I pass in 3 variables in to the stored procedure if a variable = 1 then I need to add clause to the where statement

A simple breakdown of what i am trying to do is here:

SELECT * 
FROM products 
WHERE categoryId = @categoryId
If @var1 = 1 Then
AND var = @var1
If @var2 = 1 Then
AND var2 = @var2
If @var3 = 1 Then
AND var3 = @var3

ORDER BY productName

回答1:

A typical way to do this is to put the logic directly in the where clause:

where categoryId = @categoryId and
      (@var1 <> 1 or var = @var1) and
      (@var2 <> 1 or var2 = @var2) and
      (@var3 <> 1 or var3 = @var3)