setting up a Sql Stored Procedure to accept an array as a parameter...
Ok, I'm not sure the title is exactly right, so let me explain better what it is I'm trying to do this fine evening. :)
So I have this Sql Report Services report that takes a input parameter that is currently in a combobox at the top of the report. It then feeds the value of this drop down list into my stored procedure and produces the data.
One of the abilities of this combo box is to be a multi-select type. I want to setup my stored procedure to be able to take the mutli-select input...but I'm not sure how to go about doing it.
For example, if the multi-select Combobox has the values 1 through 3...if all of them selected, the string value of that Combobox would be "1, 2, 3" which is currently getting passed into my stored procedure, which blows up because it doesn't understand what to do with the comma's.
I want to make my stored procedure more intelligent and be able to handle a comma delimited array of values. The problem is I'm not sure what this type of thing is called so I don't what I'm looking for, hehehe... It's always comes down to just knowing where to look for info doesn't it...hehehe.
Well, as always if anyone has any suggestions for me, I'm all eyes. :) Until then I'll keep digging.
Comments
Anonymous
March 14, 2006
Wow, this ended up being not as simple as I was hoping, but I did manage to figure it out.
From the report the multi-select parameter must be sent to the stored procedure using the Join function to make it into a comma delimited (or whatever delimiter you want) list...while the stored procedure needed to be modifed to read a single or multiple set of values into a temporary table, which I joined to the table with the records I was looking for.
Seemed to work out pretty well. :)Anonymous
March 15, 2006
Can you post some codeAnonymous
March 15, 2006
Sure thing, I was tired last night otherwise I would have then. ;)
For the report side of things, to turn the multi-select report parameter's output into something that can be passed into a sql stored procedure you need to use the Join function. If you had a multi-select parameter named "FooParam" it would look like this...
Join(Parameters!FooParam.Value, ",") The first parameter being passed into the Join function is the object array...the second is the delemiter you want to use.
For the Sql side of things, I added the following code to shred the input into a temp table which I later joined to the select statement that returns the data I'm interested in...in this example the parameter @inputParam is a value passed into the stored procedure with a comma delimited list
DECLARE @currentIndex int
DECLARE @nextIndex int
DECLARE @value nvarchar(50)
IF(RIGHT(@inputParam, 1) != ',')
BEGIN
-- there has to be a trailing comma so this is making sure there is one at the end of the list
SET @inputParam = @inputParam + ','
END
CREATE TABLE #MultiInput
(
InputValue nvarchar(50) NOT NULL
) ON [PRIMARY]
SELECT @currentIndex = 0, @nextIndex = CHARINDEX(',', @inputParam, @currentIndex)
WHILE (@nextIndex > 0)
BEGIN
SELECT @value = SUBSTRING(@inputParam, @currentIndex, @nextIndex - @currentIndex)
INSERT INTO #MultiInput (InputValue) VALUES (@value)
SELECT @currentIndex = @nextIndex + 1
SELECT @nextIndex = CHARINDEX(',', @inputParam, @currentIndex)
END
At this point the individual values will be contained the temp table, which can then be used to join on...Anonymous
March 15, 2006
oh yeah, let me know if you have any questions...I'm totally glad to help out if I can. :)Anonymous
April 20, 2006
regarding this post - i need to send a list of values(all are int) comma seperated froma parent report to a child report which then sends this list to a sp.Basically fron the parent report i click a hyperlink for a column which is a count of all ids grouped by month,and the child report displays details of these list of ids which make up the count.Any idea how i could implement this?Anonymous
September 08, 2006
Just wanted to say 'thanks!'. This helped me a bunch.Anonymous
January 02, 2007
I dont understand where Join(Parameters!FooParam.Value, ",") goes...Anonymous
April 16, 2007
I've just been working on the same thing and found that by creating a dataset to populate the dropdown (i.e. Select Distinct id, description) and tying that to the parameter in the main select statement in the Where clause by saying "Where contactid IN (@ContactSelection)". Then all you have to do is set up the parameter from the layout view, by selecting from the file menu Report->Report Parameters... Here you specify that the parameter ContactSelection allows multi select and is from a query... Works like a charm :)Anonymous
August 22, 2007
The comment has been removedAnonymous
August 30, 2007
The comment has been removedAnonymous
September 04, 2007
Correction: The WHERE clause needs to CAST the contactid to a varchar: "WHERE(@ContactSelection LIKE('%,' + CAST(contactid AS VARCHAR(10)) + ',%))"Anonymous
October 18, 2007
Wow...there's been a lot of random traffic on this post. :) If anyone still needs help with it, I'll try start paying more attention. It's nice to know I did something helpful.Anonymous
December 14, 2007
The comment has been removedAnonymous
December 14, 2007
The comment has been removedAnonymous
December 14, 2007
Good thinking! Never underestimate the ability of users to be dumber than you can program...Anonymous
January 09, 2008
Just wanted to post a quick thanks for the great help you provided me. This article was easy to implement and saved me lots of heartache. Thanks, Yonah W.Anonymous
January 22, 2008
I want to write a stored procedure to pass multiple select values through drop down in sql server 2005 reporting services.Anonymous
June 16, 2009
PingBack from http://fixmycrediteasily.info/story.php?id=17183