-1

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
marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
user2358810
  • 1
  • 1
  • 2

1 Answers1

4

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)
HABO
  • 14,649
  • 5
  • 37
  • 55
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709