January 11, 2012 at 10:35 am
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/
January 11, 2012 at 11:11 am
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/
January 11, 2012 at 12:25 pm
I bypassed SQL 2005 but 2008 also has an allow blank value checkbox, have you ticked that?
January 11, 2012 at 12:34 pm
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/
January 13, 2012 at 6:51 am
Have you tried a profile trace to see what is actually being passed to your SP?
January 13, 2012 at 7:54 am
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/
January 13, 2012 at 8:35 am
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
January 24, 2012 at 9:11 am
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
January 24, 2012 at 9:39 am
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
January 24, 2012 at 9:54 am
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.
January 24, 2012 at 10:13 am
wolfkillj (1/13/2012)
Welsh Corgi (1/11/2012)
After you have created the "splitter" function, your query syntax would look like this:WHERE State in (select Item from dbo.DelimitedSplit8K(@State, ','))
I tried that but I still get the same error in SSRS.
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/
January 24, 2012 at 10:44 am
mjbriggs03 (1/24/2012)
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.
Sorry, Mike - can't give you much help with tuning an Oracle query. It sounds like your query is doing a tremendous amount of work. It might be worth reviewing the business requirements and figuring out whether a "divide and conquer" strategy would help. In many situations, I've found that the costs of building, storing, and maintaining tables of precomputed results can be lower than doing a bunch of aggregations or computations on the fly. You know best your business requirements and available resources, but don't be afraid to consider creative ways to solve problems!
Jason Wolfkill
January 24, 2012 at 11:13 am
Welsh Corgi (1/24/2012)
wolfkillj (1/13/2012)
Welsh Corgi (1/11/2012)
After you have created the "splitter" function, your query syntax would look like this:WHERE State in (select Item from dbo.DelimitedSplit8K(@State, ','))
I tried that but I still get the same error in SSRS.
So if I understand your requirements correctly, you want the user to be able to execute the report without making a selection in the State parameter drop-down list, and if the user does this, the report should return results without regard to the value in the State column (i.e., the report will not be filtered by State), yes?
If so, you could set the default value of the State parameter to '-ALL-' in SSRS so that the user can view the report without making a selection in the drop-down box, then rewrite the WHERE clause in the query like this:
WHERE '-ALL-' IN (select Item from dbo.DelimitedSplit8K(@State, ',')) OR State in (select Item from dbo.DelimitedSplit8K(@State, ','))
You should have discovered that a multi-value parameter cannot have a NULL value (at least, not in SSRS 2008 - I'm pretty sure it's the same in SSRS 2005). Adding a NULL value to the drop-down list for the SSRS parameter will not help with this technique because the condition
WHERE State in (NULL)
resolves to UNKNOWN (neither TRUE nor FALSE) , and because all conditions in the WHERE clause must resolve to TRUE for any row to be returned, you will never get any rows.
If that doesn't help, could you post the full stored procedure code and more details about your SSRS parameter (is the drop-down list populated by a query, what do you have for the default value, etc.)?
Jason Wolfkill
January 24, 2012 at 12:00 pm
The delimiter is not working.:w00t:
I have the following in my Stored Procedure:
AND State IN (SELECT State FROM dbo.Split_Multi_Value_Parameters (@State, ','))
I perform a DBCC INPUTBUFFER (SPID) and it parses as follows. I have not included all 50 states:
AND State = N'AL',N'AK'
The problem is that the delimiter function does not have it's intended behavior.
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/
January 24, 2012 at 1:36 pm
This is the code for the function:
CREATE FUNCTION dbo.Split_Multi_Value_Parameters
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(max),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )
AS
BEGIN
DECLARE @tempItemList NVARCHAR(max)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(max)
SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
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/
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply