Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Condition in a Where clause? Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 3:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:34 AM
Points: 299, Visits: 498
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
Post #1519818
Posted Wednesday, December 4, 2013 4:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 25, 2014 10:09 AM
Points: 283, Visits: 1,114
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
Post #1519834
Posted Thursday, December 5, 2013 7:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:34 AM
Points: 299, Visits: 498
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
Post #1520096
Posted Thursday, December 5, 2013 8:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:29 PM
Points: 3,513, Visits: 7,565
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.

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



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520144
Posted Thursday, December 5, 2013 9:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 25, 2014 10:09 AM
Points: 283, Visits: 1,114
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.

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)

@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
Post #1520199
Posted Thursday, December 5, 2013 10:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:29 PM
Points: 3,513, Visits: 7,565
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520216
Posted Thursday, December 5, 2013 10:29 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 4,171, Visits: 3,201
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
Post #1520233
Posted Friday, December 6, 2013 8:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 1,222, Visits: 2,545
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1520628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse