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 123»»»

How to Ignore Report Parameter Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 10:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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.


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/

Post #1234188
Posted Wednesday, January 11, 2012 11:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
I have specified to allow NULL in the parameters dialogue box.

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/

Post #1234224
Posted Wednesday, January 11, 2012 12:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:15 PM
Points: 2,009, Visits: 3,440
I bypassed SQL 2005 but 2008 also has an allow blank value checkbox, have you ticked that?
Post #1234288
Posted Wednesday, January 11, 2012 12:34 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1234295
Posted Friday, January 13, 2012 6:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:42 AM
Points: 593, Visits: 2,110
Have you tried a profile trace to see what is actually being passed to your SP?
Post #1235590
Posted Friday, January 13, 2012 7:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1235657
Posted Friday, January 13, 2012 8:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 1,061, Visits: 2,574
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
Post #1235695
Posted Tuesday, January 24, 2012 9:11 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 5:16 AM
Points: 60, Visits: 98
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
Post #1240966
Posted Tuesday, January 24, 2012 9:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 1,061, Visits: 2,574
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
Post #1240989
Posted Tuesday, January 24, 2012 9:54 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 5:16 AM
Points: 60, Visits: 98
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.
Post #1240999
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse