Prev: Display RTF String
Next: SSRS 2008 report properties code window language support
From: OriginalStealth on 19 Jan 2010 08:09
stored procedure 1: exec getcolor @color = blue....this runs fine.
stored procedure 2: exec getcolors @colors in('red', 'white',
'blue').........bombs

code is

create procedure getcolors @colors nvarchar (50)
as
(select *
from hrs_by_activity
where color in(@colors)
)

need help making this work.
thanks in advance
From: Bruce L-C [MVP] on 19 Jan 2010 14:02
Below is my standard response about this. As you will see, it touches
exactly on what you are doing and why it doesn't work and what you need to
do:

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

"OriginalStealth" <OriginalStealth(a)discussions.microsoft.com> wrote in
message news:891551F0-605F-453D-BC23-4036A597DDDC(a)microsoft.com...
> stored procedure 1: exec getcolor @color = blue....this runs fine.
> stored procedure 2: exec getcolors @colors in('red', 'white',
> 'blue').........bombs
>
> code is
>
> create procedure getcolors @colors nvarchar (50)
> as
> (select *
> from hrs_by_activity
> where color in(@colors)
> )
>
> need help making this work.
> thanks in advance

 | 
Pages: 1
Prev: Display RTF String
Next: SSRS 2008 report properties code window language support