SQL SSMS Case Statement In Where Clause - With Solution

[yellowfin7]

Win 7, SSMS SQL Server 2008 r2

Good day everyone,

Today I have run into an issue regaurding case statemtents within a where clasue. Specifically within an IN statement within the where clasue.

I am wondering what the proper syntax or solution would be for the code listed below. The issue seems to be passing multiple ID's when the location is 99999

Code: Select all

WHERE
	LOC.LOC_ID	IN(
			CASE
				WHEN	@LOCATION = '99999' THEN	12345, 12346, 12347
				ELSE				@LOCATION
			END)

I'm beginning to wonder now if it is better to use boolean logic instead?

[HansV]

If @LOCATION is a string, shouldn't there be quotes around 12345 etc.? Or conversely, shouldn't you omit the quotes around '99999'?

Apart from that:

Code: Select all

WHERE LOC.LOC_ID IN (12345, 12346, 12347) AND @LOCATION = '99999' OR 
    LOC.LOC_ID = @LOCATION AND @LOCATION <> '99999'

[yellowfin7]

You are correct, I should have specified the values that are being passed.

@LOCATION is numeric and SSMS was converting '99999' to 99999 in the back end so I have removed the single quotes.

I have added the code to my where clause but it seems to slow the whole thing down a ton.

Code: Select all

WHERE
	(
	@LOCATION =   99999 
	AND LOC.LOC_ID IN  (12345, 12346, 12347)
	)
	OR
	(
	@LOCATION !=   99999
	AND LOC.LOC_ID =    @LOCATION	
	)
	AND A.AMOUNT !=   0
	AND D.DETAIL_TYPE IN   (1,10)

[HansV]

If the last two conditions should always be imposed you need to change the parentheses:

Code: Select all

WHERE
   (
   @LOCATION =   99999 
   AND LOC.LOC_ID IN  (12345, 12346, 12347)
   
   OR
   
   @LOCATION !=   99999
   AND LOC.LOC_ID =    @LOCATION   
   )
   AND A.AMOUNT !=   0
   AND D.DETAIL_TYPE IN   (1,10)

Not sure why it's slow.

[BenCasey]

Have you considered the Exists clause.

See here http://stackoverflow.com/questions/1136 ... le-columns

[yellowfin7]

I did consider that solution but becasue of the rest of the select I would not be any more efficient. I appreciate the alternate option.

After correcting the () in the boolean logic it is much faster now.... seconds instead of minutes. Often times the samllest changes result in the largest gains.

Thank you again everyone for all the help