"brian" <brian@discussions.microsoft.com> wrote in message
news:F270F0D4-6551-444A-9711-E5B0FE938063@microsoft.com...
> SQL 2005, SP2
>
> "brian" wrote:
>
>> I've been reading the forums regarding this but don't seem to understand
>> exactly how this is supposed to work. I have parameters list build from
>> query. Report Parameter is set to multi value. The SP that the report
>> uses
>> has line: PARAMETER IN (@Parameter) in the WHERE clause. When I run the
>> report and choose 'select all' I get no results though if I select just
>> one,
>> it works fine. Selecting more than one also returns no result.
>>
>> What can I possibly be doing wrong?

"Bruce L-C [MVP]" wrote:

> You answered yourself but unfortunately you answered incorrectly. Here is my
> stock answer for this:
>
> 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
>
> One other option is to create dynamic SQL in your stored procedure as well.
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
> "brian" <brian@discussions.microsoft.com> wrote in message
> news:F270F0D4-6551-444A-9711-E5B0FE938063@microsoft.com...
> > SQL 2005, SP2
> >
> > "brian" wrote:
> >
> >> I've been reading the forums regarding this but don't seem to understand
> >> exactly how this is supposed to work. I have parameters list build from
> >> query. Report Parameter is set to multi value. The SP that the report
> >> uses
> >> has line: PARAMETER IN (@Parameter) in the WHERE clause. When I run the
> >> report and choose 'select all' I get no results though if I select just
> >> one,
> >> it works fine. Selecting more than one also returns no result.
> >>
> >> What can I possibly be doing wrong?
>
>