Query all data using a parameter

Question

Query all data using a parameter

Shabbaranks

SSC Eights!

Points: 880
More actions
February 26, 2019 at 2:57 am

#409551

Hi,
My report has a drop down selection parameter with the following dataset
SELECT [SS-zzReport].order_no ,[SS-zzReport].Expr1 ,[SS-zzReport].status ,[SS-zzReport].alpha ,[SS-zzReport].customer ,[SS-zzReport].address1 ,[SS-zzReport].address2 ,[SS-zzReport].address3 ,[SS-zzReport].address4 ,[SS-zzReport].address5 ,[SS-zzReport].product ,[SS-zzReport].description ,[SS-zzReport].order_qty ,[SS-zzReport].allocated_qty ,[SS-zzReport].despatched_qty ,[SS-zzReport].customer_order_no ,[SS-zzReport].date_despatched ,[SS-zzReport].analysis_codes4 ,[SS-zzReport].key2 ,[SS-zzReport].SKUCode ,[SS-zzReport].date_required FROM [SS-zzReport] Where [SS-zzReport].date_despatched between @StartDate and @EndDate AND [SS-zzReport].address1 like '%' + @Company + '%'
If I was to add "All" to my parameter drop down how would I parse that through to my query? I've tried * with out much joy?
Thanks

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

If you mean that you want al "all" option for @Company, I'd personally pass NULL and handle it with an OR:

SELECT [SS-zzReport].order_no ,[SS-zzReport].Expr1 ,[SS-zzReport].status ,[SS-zzReport].alpha ,[SS-zzReport].customer ,[SS-zzReport].address1 ,[SS-zzReport].address2 ,[SS-zzReport].address3 ,[SS-zzReport].address4 ,[SS-zzReport].address5 ,[SS-zzReport].product ,[SS-zzReport].description ,[SS-zzReport].order_qty ,[SS-zzReport].allocated_qty ,[SS-zzReport].despatched_qty ,[SS-zzReport].customer_order_no ,[SS-zzReport].date_despatched ,[SS-zzReport].analysis_codes4 ,[SS-zzReport].key2 ,[SS-zzReport].SKUCode ,[SS-zzReport].date_required FROM [SS-zzReport] Where [SS-zzReport].date_despatched between @StartDate and @EndDate AND ([SS-zzReport].address1 LIKE '%' + @Company + '%' OR @Company IS NULL) OPTION (RECOMPILE);
I've also added the recompile option, as this turn the query into what is known as a "catch-all" query. This causes the data engine to regenerate the query plan; meaning a cached one based on a different set of parameters isn't used (which is important as @Company having a value of NULL or will likely have a big effect on the expected row count(s)).

It's also, however, worth noting that your query isn't going to he SARGable with that LIKE. Ideally the company name (I assume that's what that is) should be being stored in a separate column and you can query that. You'll likely see some significant improvement in your query if you get rid of that leading wildcard somehow.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

DesNorton SSC-Insane Points: 24279 More actions · Answer 2

Thom A - Tuesday, February 26, 2019 4:30 AM
If you mean that you want al "all" option for @Company, I'd personally pass NULL and handle it with an OR:
SELECT [SS-zzReport].order_no ,[SS-zzReport].Expr1 ,[SS-zzReport].status ,[SS-zzReport].alpha ,[SS-zzReport].customer ,[SS-zzReport].address1 ,[SS-zzReport].address2 ,[SS-zzReport].address3 ,[SS-zzReport].address4 ,[SS-zzReport].address5 ,[SS-zzReport].product ,[SS-zzReport].description ,[SS-zzReport].order_qty ,[SS-zzReport].allocated_qty ,[SS-zzReport].despatched_qty ,[SS-zzReport].customer_order_no ,[SS-zzReport].date_despatched ,[SS-zzReport].analysis_codes4 ,[SS-zzReport].key2 ,[SS-zzReport].SKUCode ,[SS-zzReport].date_required FROM [SS-zzReport] Where [SS-zzReport].date_despatched between @StartDate and @EndDate AND ([SS-zzReport].address1 LIKE '%' + @Company + '%' OR @Company IS NULL) OPTION (RECOMPILE);
I've also added the recompile option, as this turn the query into what is known as a "catch-all" query. This causes the data engine to regenerate the query plan; meaning a cached one based on a different set of parameters isn't used (which is important as @Company having a value of NULL or will likely have a big effect on the expected row count(s)).
It's also, however, worth noting that your query isn't going to he SARGable with that LIKE. Ideally the company name (I assume that's what that is) should be being stored in a separate column and you can query that. You'll likely see some significant improvement in your query if you get rid of that leading wildcard somehow.

Note that the OR @Company IS NULL will also cause a full table/index scan.

How to post data/code on a forum to get the best help.

Shabbaranks SSC Eights! Points: 880 More actions · Answer 3

Thanks I've got to be honest I don't fully understand your reply am I correct in thinking if the user doesn't select anything then it parses all? I managed to get my query to work by setting the value of all in the Parameter label and the value of that label to % which seems to return all.