RS2005 multi-select parameter

How do I use the mult-select parameter option on a report and pass it
to the stored procedure?
ie. if I have a multi-select list of employees and I pass in the
employeeid to the proc
would I?
create procedure show_employees(@.empid int)
as
select * from employees where empid in (@.empid)
would that work?
Regards,
Tom OlthoffNope. The stored procedure won't work (note that SQL will work in RS).
Why it doesn't work has nothing really to do with RS but has to do with
Stored Procedures in SQL Server. You cannot do the following in a stored
procedure. Let's say you have a Parameter called @.MyParams Now you can map
that parameter to a multi-value parameter but if in your stored procedure
you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.What you can do
is to have a string parameter that is passed as a multivalue parameter and
then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<OTSolutions@.shaw.ca> wrote in message
news:1144854681.805431.214920@.i40g2000cwc.googlegroups.com...
> How do I use the mult-select parameter option on a report and pass it
> to the stored procedure?
> ie. if I have a multi-select list of employees and I pass in the
> employeeid to the proc
> would I?
> create procedure show_employees(@.empid int)
> as
> select * from employees where empid in (@.empid)
> would that work?
> Regards,
> Tom Olthoff
>|||You can do this using XML I believe.
Pass an XML string that looks like this:
<root>
<param1>
<multiParam>value1</multiParam>
<multiParam>value2</multiParam>
<multiParam>value3</multiParam>
</param1>
</root>
And then use SQL's readXML function to read multiple values from the
XML.|||The one problem with that approach is its not really a list of
parameters its one paramter that is a string of XML.
regards,
Stas K.|||You can read about this approach with the sp_XML_PrepareDocument
function:
http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx
as for the actual access to the xml:
OPENXML( @.XMLString, N'/root/param1/multiparam/*')
http://msdn2.microsoft.com/en-us/library/ms186918(SQL.90).aspx
regards,
Stas K.