January 24, 2012 at 2:14 pm
Your splitter function returns the expected results, but the code in the stored proc should select the column Item from the function in the subselect in the WHERE clause, not State. Does that help?
Jason Wolfkill
January 24, 2012 at 3:11 pm
Not really..
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 3:58 pm
wolfkillj (1/24/2012)
Your splitter function returns the expected results, but the code in the stored proc should select the column Item from the function in the subselect in the WHERE clause, not State. Does that help?
The function does not return the required results and there is a SELECT in the WHERE Clause.
WHERE Comp.Status = 'A'
AND State IN (SELECT State FROM dbo.Split(@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 25, 2012 at 12:40 pm
Welsh Corgi (1/24/2012)
wolfkillj (1/24/2012)
Your splitter function returns the expected results, but the code in the stored proc should select the column Item from the function in the subselect in the WHERE clause, not State. Does that help?The function does not return the required results and there is a SELECT in the WHERE Clause.
WHERE Comp.Status = 'A'
AND State IN (SELECT State FROM dbo.Split(@State, ','))
Look at this line in the definition of your splitter function dbo.Split_Multi_Value_Params() that you posted above:
RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )
The function returns a table with a single column named Item. The code in your proc - WHERE State IN (SELECT State FROM dbo.Split_Multi_Value_Params(@State, ',')) - would generate error 207 - invalid column name 'State' (unless of course your function dbo.Split() in the code snippet you just posted is different from the function dbo.Split_Multi_Value_Params() that you previously posted). Have you ascertained that the code in your proc SELECTs the correct column name from the function?
Have you tested the function like this:
SELECT Item FROM dbo.Split_Multi_Value_Params('AZ,TN,NY', ',')
to see if it returns the expected output (as I did before I stated that it does)?
Do you get results when you EXECUTE the stored procedure directly in SSMS (or your preferred client software)?
If you confirm that you have corrected the small but significant mistake in the stored proc code, that the function returns a table with a column named Item populated the values from the delimited string, that you get results when you EXECUTE the stored procedure directly, and that you still do not get any results in your SSRS report, please post more information about how you are generating the report (i.e., in BIDS, with SSRS Report Manager, with the .NET Report Viewer control, by passing paramters to SSRS in the URL, etc.) and what happens when you try to view the report (e.g., an error message appears, the report appears to be running but never completes, etc.). Also, if you can SELECT @@ERROR on the same connection after you try to run the report, what is the output?
Jason Wolfkill
January 25, 2012 at 12:50 pm
The function is wrong.
Do you have a tested solution with that function?:w00t:
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 25, 2012 at 1:03 pm
Welsh Corgi (1/25/2012)
The function is wrong.Do you have a tested solution with that function?:w00t:
Here is a function that splits VARCHAR delimited strings up to 8,000 characters in length - as the comment in the code indicates, this is Jeff Moden's creation:
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE
AS
/*****
Description: Splits delimited string values up to varchar(8000) into table with columns ItemNumber and Item.
Based Jeff Moden's latest work, described at http://www.sqlservercentral.com/articles/Tally+Table/72993/
*****/
RETURN
with cteTally(N) AS (--==== This provides the "zero base" tally table and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0
UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) N FROM dbo.tally
)
,cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
If you don't already have a tally table (called dbo.tally in my function), you will need to create one. Jeff Moden explains how to create one here: http://www.sqlservercentral.com/articles/T-SQL/62867/
I have used this function numerous times to handle SSRS 2005 multivalued parameters in stored procedures in the way I described to you.
Jason Wolfkill
January 25, 2012 at 1:05 pm
Welsh Corgi (1/25/2012)
The function is wrong.Do you have a tested solution with that function?:w00t:
Oh, and by the way, I tested the function you posted earlier. It definitely works properly on SQL Server 2008, although I wouldn't recommend this kind of function for the reasons explained by Jeff Moden here:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Jason Wolfkill
January 25, 2012 at 1:07 pm
ok, thanks but I get the same error.
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 25, 2012 at 4:26 pm
wolfkillj (1/25/2012)
Welsh Corgi (1/25/2012)
The function is wrong.Do you have a tested solution with that function?:w00t:
Oh, and by the way, I tested the function you posted earlier. It definitely works properly on SQL Server 2008, although I wouldn't recommend this kind of function for the reasons explained by Jeff Moden here:
Well I'm not using 2008 and if I was I would not need this work around.
Thanks for your suggestion but it is time to move on.
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 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply