Condition in a Where clause?

Question

Post reply

Condition in a Where clause?

jbalbo

SSCertifiable

Points: 7580
More actions
December 4, 2013 at 3:46 pm

#302102

Hi
Not sure if I titled this correctly
but what I want to do is add a condition in a where statement when a parameter is not a value
WHERE
(dbo.CostCenter.Code = @code) AND ...
This is what I need to be conditional...
(CASE WHEN @Zip <> 'All' THEN dbo.Address.Zip in (@Zip)) otherwise I don't want to evaluate @Zip
Thanks In advance
Joe

Viewing 8 posts - 1 through 7 (of 7 total)

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

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 1

WHERE

(dbo.CostCenter.Code = @code) AND

(@Zip = 'All' OR dbo.Address.Zip in (@Zip))

This will work but won't use any index on the Zip column.

The other option is to build dynamic SQL, but with the usual warnings re SQL injection, compilation overhead, etc.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

jbalbo SSCertifiable Points: 7580 More actions · Answer 2

Hi Richard,

Thank you for the info.

That works, but since I had time to think I just queried the active zip codes and its an SSRS report so I allowed for multiple values

Which brings me to my next question why do i get an error when I show multiple values in the parameter

I must have to declare it differently?

But I will post the question

Thanks Again

Joe

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You can't use a variable with a comma-separated values string to be evaluated with IN().

You could as well try the Delimited String Splitter[/url].

Read the article and if you have any questions, feel free to ask.

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

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 4

Luis Cazares (12/5/2013)
You can't use a variable with a comma-separated values string to be evaluated with IN().
You could as well try the Delimited String Splitter[/url].
Read the article and if you have any questions, feel free to ask.

@luis,

Actually you can use a variable inside IN if it is called from SSRS. The variable is replaced in the query before submitting it to SQL Server.

So

dbo.Address.Zip in (@Zip)

Is converted by SSRS to

dbo.Address.Zip in ('Value1','Value2','Valuen')

Unfortunately the first part of my first answer won't work if called from SSRS, as it will try to evaluate 'Value1','Value2','Valuen' = 'All', which is obviously invalid.

So my first answer demonstrates psychic ability (for knowing he was talking about SSRS) and muppetry (because it won't work as written) :ermm:

@jbalbo

If you are allowing multiple values and there is no option to select "All", just change it to

WHERE

dbo.CostCenter.Code = @code AND

dbo.Address.Zip in (@Zip)

And if called from outside SSRS, the function referenced by Luis will work.

Sorry for the confusion.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Actually you can use a variable inside IN if it is called from SSRS. The variable is replaced in the query before submitting it to SQL Server.

My knowledge in SSRS is very limited, so it's good to know this. 😀

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

Luis Cazares (12/5/2013)
Actually you can use a variable inside IN if it is called from SSRS. The variable is replaced in the query before submitting it to SQL Server.
My knowledge in SSRS is very limited, so it's good to know this. 😀

Yeah, mine too. I can spell SSRS, but that's about it.

I'd bet the DelimitedSplit8K is faster than SSRS. 😀

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 7

Ed Wagner (12/5/2013)
Luis Cazares (12/5/2013)
Actually you can use a variable inside IN if it is called from SSRS. The variable is replaced in the query before submitting it to SQL Server.
My knowledge in SSRS is very limited, so it's good to know this. 😀
Yeah, mine too. I can spell SSRS, but that's about it.
I'd bet the DelimitedSplit8K is faster than SSRS. 😀

I don't know that splitting a comma-separated string in the database would necessarily be faster (at least within the database itself). When SSRS prepares a T-SQL query and submits it to the database, it substitutes the appropriate literal values in the IN clause for the SSRS variable, so the query arrives at the database ready for execution. When SSRS calls a stored procedure that executes the same query, it passes the SSRS variable to the stored procedure parameter as a comma-separated string that must be split before the values can be evaluated in an IN clause, so there's an extra step there.

Of course, the down side of SSRS datasets that rely on a query that is coded in the report (rather than a stored proc) is that any changes to the query (because of changes in business logic, schema changes, etc.) must be made in the report (and possibly in several reports, if more than one report uses the same query) versus a single change to a stored proc in the database. IMHO, the value of using stored procedures to serve data to SSRS reports far outweighs the cost of the extra step of splitting comma-separated strings when necessary.

Jason Wolfkill