April 21, 2009 at 7:18 am
I have been able to use multi-value parameters in a report and pass them to a stored procedure without doing any heavy lifting like this. You need to create a split function shown below and use the function and the IN statement in the WHERE clause and your done.
Report Parameter
Name: FilterRegionId
Data type: Integer
Prompt: FilterRegionId
Hidden: unchecked
Internal: unchecked
Multi-value: checked
Allow null value: unchecked
Allow blank value: checked (disabled checkbox)
Available values: Non-queried
Default values: Non-queried
Stored Procedure
Parameter: @FilterRegionId AS varchar(max)
WHERE Clause: WHERE ORG.OrganizationId IN(SELECT Item FROM dbo.Split(ISNULL(@FilterRegionId, ORG.OrganizationId), ','))
Split Function
------------------------
CREATE FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
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
April 21, 2009 at 7:22 am
Alex
I am so glad you posted this article. It pertain specifically to an issue I am trying to resolve in two reports I am finalizing today. I am going to try what you have suggested here but am bot familar yet with CTE, the WHILE loop, or as Simon mentioned in the reply post a Split function but I will try each and hopefully come up with a solution....Thanks for the help!
Christina
April 21, 2009 at 7:24 am
But it feels so dirty doing string manipulation in a database to me with all that iterative processing... tally tables would be nicer, but quite a mindset jump.
April 21, 2009 at 7:25 am
In real project I never used comma. I am using any non-keybord character, for example ASCII = 135. The comma was used for simplisity.
April 21, 2009 at 7:29 am
Most of my fellow audience miss the point. For some reason your focus comes to how to parse a string, however this article how prevent array passing to stored procedure.
April 21, 2009 at 7:33 am
I think I found it somewhere on the net... sorry to original author for not including the credits...
but I found it's quite easy to use Table-valued function to handle the multi-parameters.
You can create the function with this code:
CREATE FUNCTION [dbo].[fn_MVParam] (@RepParam nvarchar(max), @Delim char(1)= ',')
RETURNS @Values TABLE (Param varchar(1024))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(1024)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
And then within the stored procedure which accepts parameter (or one of them) @MultiValuedParam add this to your data retrieval:
... INNER JOIN dbo.fn_MVParam( @MultiValuedParam , ',') pt on pt.Param = ColumnNameToMatchValues
Seems to be pretty efficient.
April 21, 2009 at 7:33 am
Irina, I will research this problem for you. I believe that you need to check array count, Then throw an error when count greater then expected value.
April 21, 2009 at 7:40 am
Thank you very much, by using store procedure I can store in temporary table only first 5 values. But I need to do without store procedure. I need to change XML code in report property because using our environment it is much better(performance) to send query to data warehouse.
April 21, 2009 at 7:44 am
Using any one of the functions provided in this discussion that returns a table containing the multi-value selections, its a simple query to check the count..
SET @PARMCNT = (SELECT SUM(1) FROM Reporting.dbo.ParmSplit(@ParmValues,','))
For user convenience, I'd rather have the check done on the interface side while they are selecting the values, or right when they click on "view report".
April 21, 2009 at 7:47 am
We created a table function (below) that parses the values out. All you need to do then is inside of the where clause: value IN (select * from dbo.PassingMultiSelect_Parameters(@Parameters,',')). We now use this on several of our automated reports with no issues.
CREATE FUNCTION [dbo].[Passing_MultiSelect_Parameters] (
@MultiSelectParameter nvarchar(max),
@Separator nvarchar(10)
)
RETURNS @resultTable TABLE (result nvarchar(max))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value nvarchar(max)
SET @MultiSelectParameter = @MultiSelectParameter + @Separator
WHILE patindex('%' + @Separator + '%', @MultiSelectParameter) 0
BEGIN
SELECT @separator_position = patindex('%' + @Separator + '%', @MultiSelectParameter)
SELECT @array_value = left(@MultiSelectParameter, @separator_position - 1)
INSERT @resultTable
VALUES (Cast(@array_value AS nvarchar(max)))
SELECT @MultiSelectParameter = stuff(@MultiSelectParameter , 1, @separator_position, '')
END
RETURN
END
GO
April 21, 2009 at 7:48 am
Alex Grinberg (4/21/2009)
Most of my fellow audience miss the point. For some reason your focus comes to how to parse a string, however this article how prevent array passing to stored procedure.
Alex, I guess we are missing the point of why you do not want to pass an array to the stored procedure. Passing a array to the stored procedure works very well and seems much more straight forward in practice. I will give your article another go over to see if I am missing something but I feel you took a pipe wrench to adjust a watch. We are just showing you that you may work with what SQL Reporting Services provides. I would not be surprised if I overlooked something and did not need to create my own split function. If there is a system function which does the same thing I would hope someone would point that out as well.
Thanks for writing the article as many people have probably struggled with this as well.
April 21, 2009 at 7:50 am
You can limit to 5 by having a second parameter that is based on the first.
Set the query to use the second parameter and set the default value for the second parameter to something like.
Where lastname is the parameter the user selects. This results in the query being executed with an invalid value. You can then in the report display a message saying they need to select 5,"-1",Join (Parameters!lastname.Value ,","))
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
April 21, 2009 at 7:55 am
I ran into this issue and needed a solution which could handle a large amount of values for the multi-value parameter, so I transferred the string into a #temp table and linked the #temp table to my query. Copy & Paste the below code in SQL Server Management Studio to see the template i've been using for this.
--------------------------------------------------------------
-- Multi Value Parameters(MVP) Temp Table For Stored Proc --
--------------------------------------------------------------
USE [Enter Database Name]
--------------------------------------------------------------
-- Used ONLY For Testing In Management Studio --
--------------------------------------------------------------
/*
DECLARE
@MvP varchar(MAX)
SET @MvP = '1234567,7654321' --Example String For Testing--
*/
--------------------------------------------------------------
-- Used To Create Procedure --
--------------------------------------------------------------
/*
GO
CREATE PROCEDURE [dbo].[Enter Procedure Name]
@MvP varchar(MAX)
AS
*/
--------------------------------------------------------------
-- Converts String To Column In Table --
--------------------------------------------------------------
CREATE TABLE #tempMVP
(tMVP varchar(20))
DECLARE
@MVP1 varchar(50),
@MVP2 varchar(MAX),
@LOC int
SET @MVP2 = @MvP + ','
WHILE len(@MVP2) > 0
BEGIN
SET @LOC = charindex(',',@MVP2)
SET @MVP1 = left(@MVP2,@LOC-1)
SET @MVP2 = substring(@MVP2,@LOC+1,len(@MVP2))
INSERT INTO #tempMVP VALUES (@MVP1)
END
--------------------------------------------------------------
-- Start of Select Query--
--------------------------------------------------------------
BEGIN
SELECT
temp.tMVP AS [MVP]
--------------------------------------------------------------
-- NOTES--
----
-- Add other fields from your joined tables as needed --
--------------------------------------------------------------
FROM
#tempMVP AS temp
--------------------------------------------------------------
-- NOTES--
----
-- Add other tables joined to #tempMVP table--
-- EXAMPLE: LEFT JOIN [Table_Name] ON [temp].[tMVP] =--
-- [Table_Name].[Field_Name]--
----
-- Or use a SubQuery to show all entered values for the MVP --
-- EXAMPLE: LEFT JOIN (SubQuery) AS SubQuery ON--
--[temp].[tMVP] = SubQuery.[Field_Name]--
----
--------------------------------------------------------------
ORDER BY
[MVP]
END
DROP TABLE #tempMVP
--------------------------------------------------------------
-- END --
--------------------------------------------------------------
April 21, 2009 at 8:09 am
richteel (4/21/2009)
Alex Grinberg (4/21/2009)
Most of my fellow audience miss the point. For some reason your focus comes to how to parse a string, however this article how prevent array passing to stored procedure.Alex, I guess we are missing the point of why you do not want to pass an array to the stored procedure. Passing a array to the stored procedure works very well and seems much more straight forward in practice. I will give your article another go over to see if I am missing something but I feel you took a pipe wrench to adjust a watch. We are just showing you that you may work with what SQL Reporting Services provides. I would not be surprised if I overlooked something and did not need to create my own split function. If there is a system function which does the same thing I would hope someone would point that out as well.
Thanks for writing the article as many people have probably struggled with this as well.
I had another look at the article and I see that there may be a difference in that I have used multi-value parameters with integer values whereas it looks like you are using string values. I can see that there is a potential for an issue when using string values and a different approach may be needed.
Thanks again
April 21, 2009 at 8:22 am
Great article, Alex! lots of useful tips and information, thanks. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply