Multi-Select Parameters

I think I am really missing something here. I am trying to set up a
multi parameter dataset. I have read through all the postings and am
looking at the Wrox book SQL Server 2005 Reporting Services. It
states the following query will work
Select ProductSubcategoryId, [Name]
>From Production.ProductSubCategory
Where ProductSubCategoryID IN( @.ProductSubCategories )
so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
a nvarchar(150)
I have turned the parameter on the RS side as multiselect and am
passing 2 values through to this SP
I have tried testing this just through sql management studio to no
avail.
What am I missing?What you are missing is a SQL Server thing. It is not a RS issue. You cannot
pass a string and use it in an in clause in a stored procedure. If you have
the below statement in the dataset (i.e. not calling a SP it will work).Here
is a previous post of mine on the subject:
What 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
<bysnyder@.hotmail.com> wrote in message
news:1166195712.306140.100760@.j72g2000cwa.googlegroups.com...
>I think I am really missing something here. I am trying to set up a
> multi parameter dataset. I have read through all the postings and am
> looking at the Wrox book SQL Server 2005 Reporting Services. It
> states the following query will work
>
> Select ProductSubcategoryId, [Name]
>>From Production.ProductSubCategory
> Where ProductSubCategoryID IN( @.ProductSubCategories )
> so, I have created a SP on sql 2005 and set up @.ProductSubCategories as
> a nvarchar(150)
> I have turned the parameter on the RS side as multiselect and am
> passing 2 values through to this SP
> I have tried testing this just through sql management studio to no
> avail.
> What am I missing?
>