?? CASE in a where statement

Question

Post reply

?? CASE in a where statement

jbalbo

SSCertifiable

Points: 7583
More actions
November 6, 2013 at 10:30 am

#282080

Hi
Looking to have a where clause dependent on a parameter value
Parameter = @ALL
it currently uses
Where....
AND (CLIENT_IDENTIFIER_TYPE.Code between @Code and @CODE2 )
I want to now have something like
WHERE ....
CASE WHEN @All <>'Y'
then AND (CLIENT_IDENTIFIER_TYPE.Code between @Code and @CODE2 )
else
-- "Don't evaluate it"
end
or would I have to do something like
IF @All = 'y'
BEGIN
SELECT * FROM Table....
Where....
AND (CLIENT_IDENTIFIER_TYPE.Code between @Code and @CODE2 )
END
ELSE
BEGIN
SELECT * FROM Table....
"No where ..."
END
Thanks
Joe
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Check this article, as it might help you to improve performance.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

The "simple" fix here is to use something like this:

where (@All <> 'Y' and CLIENT_IDENTIFIER_TYPE.Code between @Code and @CODE2)

or @All = 'Y'

However, this now is exactly the pattern that the link that Luis provided is discussing.

In your situation I would instead consider using multiple execution paths (which you eluded to in your original post).

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/