-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
  • 732,580
  • 175
  • 1,330
  • 1,459
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
  • 15,314
  • 5
  • 39
  • 57
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786