Randyg1414
asked on
Determine if a value in a multi value report parameter exists in an SQL field
Hello,
I have a report that uses a multi valued parameter. For sake of example, lets say the parameter (@test) is a list of products: proda, prodb, prodc. Also, all 3 values have been chosen by default. I want to take @test and see if any of the parameter values exist in a field (Field1) that is a comma delimited list of products such as: "proda,prodc". The first step would be in parsing @test for its invididual values, but it fails. If I write some code to look for the comma in an sql parameter called @test it works.
DECLARE @strSingleValue INT
DECLARE @test VARCHAR(100)
SET @test = 'Value1,Value2,Value3'
SET @intStringPosition = 1
SELECT @intStringLocator = CHARINDEX(',', (@test),@intStringPosition
select Field1=substring(@test, @intStringPosition, @intStringLocator - @intStringPosition)
However, If I use a muti value report parameter called @test and take out the DECLARE statement in the same sql code, the query fails saying the comma is invalid: Incorrect syntax near ','.
We have some VB code that does do this parsing and use it in a filer, but it is very slow. I am looking for a faster solution.
SQL: 2008
Report Builder: 3.0
I have a report that uses a multi valued parameter. For sake of example, lets say the parameter (@test) is a list of products: proda, prodb, prodc. Also, all 3 values have been chosen by default. I want to take @test and see if any of the parameter values exist in a field (Field1) that is a comma delimited list of products such as: "proda,prodc". The first step would be in parsing @test for its invididual values, but it fails. If I write some code to look for the comma in an sql parameter called @test it works.
DECLARE @strSingleValue INT
DECLARE @test VARCHAR(100)
SET @test = 'Value1,Value2,Value3'
SET @intStringPosition = 1
SELECT @intStringLocator = CHARINDEX(',', (@test),@intStringPosition
select Field1=substring(@test, @intStringPosition, @intStringLocator - @intStringPosition)
However, If I use a muti value report parameter called @test and take out the DECLARE statement in the same sql code, the query fails saying the comma is invalid: Incorrect syntax near ','.
We have some VB code that does do this parsing and use it in a filer, but it is very slow. I am looking for a faster solution.
SQL: 2008
Report Builder: 3.0
In SSRS (at least in SQL 2005), you just used "=" for the multi-valued parameter: SSRS itself is coded to realize that it is a list, so SQL will write the code to expand it to check for every value.
Thus, multi-valued parameters passed into a stored proc, outside of SSRS, for example, are very different from SSRS parameters, because you must handle the splitting yourself.
However, this is a very fast, table-valued function you can use to split strings in your db. And since the input string -- "@test" or "@params" doesn't change after it's initially set, you can split the @input once, stored it in a keyed table, then use it to match to the split values from the table-valued function.
If I'm on the right track, and you want to pursue something like this, just let me know.
Thus, multi-valued parameters passed into a stored proc, outside of SSRS, for example, are very different from SSRS parameters, because you must handle the splitting yourself.
However, this is a very fast, table-valued function you can use to split strings in your db. And since the input string -- "@test" or "@params" doesn't change after it's initially set, you can split the @input once, stored it in a keyed table, then use it to match to the split values from the table-valued function.
If I'm on the right track, and you want to pursue something like this, just let me know.
CREATE FUNCTION dbo.Split (@seprator char(1), @str varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@seprator, @str)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@seprator, @str, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@str, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO
SELECT * FROM dbo.Split(',', 'Value1,Value2,Value3')
SELECT * FROM dbo.Split('|', 'Value1|Value2|Value3')
SELECT * FROM dbo.Split(' ', 'Value1 Value2 Value3')
GO
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@seprator, @str)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@seprator, @str, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@str, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO
SELECT * FROM dbo.Split(',', 'Value1,Value2,Value3')
SELECT * FROM dbo.Split('|', 'Value1|Value2|Value3')
SELECT * FROM dbo.Split(' ', 'Value1 Value2 Value3')
GO
A while ago I wrote an article about how SSRS treats multi-valued parameters in the context of a stored procedure. You'll probably find it an interesting read: Reporting On Data From Stored Procedures (part 2)
ASKER
That is a great function, and it would definitely be part of the solution. I tried this code in a report and printed the results successfully in a grid.
DECLARE @test1 VARCHAR(100)
SET @test1 = 'value1,value2,value3'
SELECT * FROM dbo.Split(',', @test1)
Now, when I modify the code to use a report parameter @test1 like this:
USE hsd_clientsw
SELECT * FROM dbo.Split(',', @test1)
It succeeds as a long as test1 is set as a single value parameter. When I change @test1 to have the values 2011 and 2012 as defaults in the parameters definition (through the report writer ui), I get the following error message:
Procedure or function dbo.Split has too many arguments specified.
--------------------------
Query execution failed for dataset 'test1'.
--------------------------
An error has occurred during report processing.
--------------------------
An error occurred during local report processing.
Whether or not I run the dbo.split function as a function or just run the code directly, I get the same results. This is what I was explaining at the very beginning of my request (though in a quite convoluted way!).
DECLARE @test1 VARCHAR(100)
SET @test1 = 'value1,value2,value3'
SELECT * FROM dbo.Split(',', @test1)
Now, when I modify the code to use a report parameter @test1 like this:
USE hsd_clientsw
SELECT * FROM dbo.Split(',', @test1)
It succeeds as a long as test1 is set as a single value parameter. When I change @test1 to have the values 2011 and 2012 as defaults in the parameters definition (through the report writer ui), I get the following error message:
Procedure or function dbo.Split has too many arguments specified.
--------------------------
Query execution failed for dataset 'test1'.
--------------------------
An error has occurred during report processing.
--------------------------
An error occurred during local report processing.
Whether or not I run the dbo.split function as a function or just run the code directly, I get the same results. This is what I was explaining at the very beginning of my request (though in a quite convoluted way!).
Please specify the error with code that will be better to understand.
ASKER
I gave it above, but here it is:
Procedure or function dbo.Split has too many arguments specified.
--------------------------
Query execution failed for dataset 'test1'.
--------------------------
An error has occurred during report processing.
--------------------------
An error occurred during local report processing.
Procedure or function dbo.Split has too many arguments specified.
--------------------------
Query execution failed for dataset 'test1'.
--------------------------
An error has occurred during report processing.
--------------------------
An error occurred during local report processing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. That answers that question. I appreciate the help.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for Randyg1414's comment #a39191035
for the following reason:
I tested it and it worked very simply. It is what I was looking for.
Accepted answer: 0 points for Randyg1414's comment #a39191035
for the following reason:
I tested it and it worked very simply. It is what I was looking for.
Glad I could help! But it looks like you selected the wrong comment as answer... I'll object so it can get corrected.
ASKER
My apologies. It should be worth 500 points! It looks like it is now accepted directly.
ASKER
Are multi value report parameters passed into SQL equivalent to SQL report parameters?
Regards,
Randy.