SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Ignore Report Parameter


How to Ignore Report Parameter

Author
Message
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10114 Visits: 4894
I modified a Stored Procedure to allow the parameter to have a Null Value.

I checked the Allow Nulls in the Parameters Dialogue Box but I get an error that I must select the parameter.Unsure

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10114 Visits: 4894
I have specified to allow NULL in the parameters dialogue box.Unsure

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3987 Visits: 5807
I bypassed SQL 2005 but 2008 also has an allow blank value checkbox, have you ticked that?
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10114 Visits: 4894
I added a Null Value to the table that is used to populate the list-box, I select Null but I get no results.

The Stored Procedure has the following criteria:

AND ((@State IS NULL) OR (State = @State))



I tried the following so I could get a multi-value drop-down checkbox but that did not work either:


AND ((@State IS NULL) OR (State IN (@State)))



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Steven.Howes
Steven.Howes
SSC Eights!
SSC Eights! (872 reputation)SSC Eights! (872 reputation)SSC Eights! (872 reputation)SSC Eights! (872 reputation)SSC Eights! (872 reputation)SSC Eights! (872 reputation)SSC Eights! (872 reputation)SSC Eights! (872 reputation)

Group: General Forum Members
Points: 872 Visits: 2340
Have you tried a profile trace to see what is actually being passed to your SP?
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10114 Visits: 4894
Steven.Howes (1/13/2012)
Have you tried a profile trace to see what is actually being passed to your SP?


I need to use a function but I had to switch to some DBA tasks and it is on hold.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 2582
Welsh Corgi (1/11/2012)
I added a Null Value to the table that is used to populate the list-box, I select Null but I get no results.

The Stored Procedure has the following criteria:

AND ((@State IS NULL) OR (State = @State))





In the past, I have avoided this issue by creating a dummy value for the SSRS parameter and making it the default value. Usually, I would display this to the user (i.e., set the label for the value) as "-ALL-" or something like that and set the value to something that would never be an actual value in the database. For example, for a State parameter that accepts two-letter abbreviations for U.S. states, I would set the value to 'XX' and the label to "-ANY-" or "-ALL-". Then, the query syntax in the stored procedure would look like this:

WHERE @State = 'XX' OR @State = State

This makes it certain that the value you pass to the stored proc parameter from SSRS will yield the desired result. Be sure to comment the stored proc code to explain the significance and use of the dummy value.

I also have used a little trick to make sure the dummy value appears at the top of the drop-down list. I wrote the query to populate the drop-down list like this:


select
StateName as paramLabel
,StateAbbr as paramValue
,2 as ordinal

from dbo.States

union all

select '-ALL-', 'XX', 1

order by ordinal, paramLabel


I used the artificial ordinal column to force the dummy value to the top of the list.


A friendly suggestion: If you haven't read Gail Shaw's article on "catch-all" queries, it's worth the time. She outlines the performance pitfalls of query syntax like that you cite above. Dynamic SQL most likely will improve the performance of your query when you want to allow some input parameters to be NULL. Of course, dynamic SQL may not be possible in your environment, so if not, please ignore this suggestion!



I tried the following so I could get a multi-value drop-down checkbox but that did not work either:


AND ((@State IS NULL) OR (State IN (@State)))





Unfortunately, SSRS 2005 passes the selected values from a drop-down list for a multi-valued parameter to SQL Server as a comma-delimited string. That's why your "State in (@State)" syntax doesn't work - to SQL Server, it looks like "State in ('TN, AR, MS')", which of course is not the desired syntax of "State in ('TN', 'AR', 'MS')". You have to parse the comma-delimited string to get the individual values. Jeff Moden has developed a very efficient way to do this:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

After you have created the "splitter" function, your query syntax would look like this:

WHERE State in (select Item from dbo.DelimitedSplit8K(@State, ','))

If you need to refer to the values contained in @State several times in the stored proc, you could also insert the results of the subselect above into a table variable or temporary table so that you only have to call the splitter function once.

Hope that helps!

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
mjbriggs03
mjbriggs03
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 102
I literally just wrote a parameter that I wanted to allow the user to use OR not use, their choice. It was a range parameter for population figures where the user types in the range (non-queried). So, here's the syntax..

((A."Population" BETWEEN (:MinPopulation) AND (:MaxPopulation)) OR ((:MinPopulation IS NULL) AND (:MaxPopulation IS NULL)))

Now, I checked off ALLOW NULL VALUES and the set the default values to NULL and it seems to be working just fine.

Oh, I'm working out of an Oracle database hence the reason you're seeing ':' instead of '@.'

Hope this is somewhat helpful

Mike
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1454 Visits: 2582
mjbriggs03 (1/24/2012)
I literally just wrote a parameter that I wanted to allow the user to use OR not use, their choice. It was a range parameter for population figures where the user types in the range (non-queried). So, here's the syntax..

((A."Population" BETWEEN (:MinPopulation) AND (:MaxPopulation)) OR ((:MinPopulation IS NULL) AND (:MaxPopulation IS NULL)))

Now, I checked off ALLOW NULL VALUES and the set the default values to NULL and it seems to be working just fine.

Oh, I'm working out of an Oracle database hence the reason you're seeing ':' instead of '@.'

Hope this is somewhat helpful

Mike


Hi Mike,

I noted that you are executing that query against an Oracle DB, but wanted to point out for others' sake that, while this syntax yields the desired results, it has some significant performance drawbacks in SQL Server queries. See this seminal blog post on the topic:

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

Does Oracle handle this kind of query syntax differently?

Regards,

Jason

I noted your comment that you are executing that query against an Oracal

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
mjbriggs03
mjbriggs03
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 102
Good question and I'm not sure haha. My query is about 32,000 characters long with nested selects, joins, etc. A real doozy. I've added two parameters thus far and run is only about 2-3 seconds. That's acceptable to me. Do you think it could run faster? I'm all ears haha.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search