Can you use a CASE in a WHERE statement.

[macmac1]

Hi all,

I am trying to do the following.

Pass a value in to my SP.
Based on that value use the condition.

----------------------------------------
WHERE
CASE
WHEN @switch = 1 THEN ( r.id in (@rep) and (m.merchant_active=1))
ELSE (m.merchant_alias in (@merchant_alias) and (m.merchant_active=1))
END

 

[tlbroadbent]

You can't do it exactly that way. You must name the columns in the Where clause. They cannot be inside the CASE Function. You can do something like this.

...
WHERE m.merchant_active=1
And r.id=
CASE WHEN @switch = 1
THEN @rep
Else r.id
End
And m.merchant_alias=
CASE WHEN @switch <> 1
THEN @merchant_alias
Else m.merchant_alias
End

If you don't want to do it this way, you can create and execute a dynamic SQL statement.

Declare @sql varchar(400)

Set @sql='Select * From table1 r' +
' Join table2 m On r.id=m.id' +
' Where m.merchant_active=1'

If @swicth=1
Set @sql=@sql+' And r.id=' + str(@rep)
Else
Set @sql=@sql+' And m.merchant_alias=' + @merchant_alias

Exec(@sql) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.