SSRS Multi-Value parameters with Stored Procedures

I've figured out how to send multi-value parameters to my stored
procedure using a split function when my parameter is an integer.
However I need to do the same thing with a character parameter.  It
works for the first value, but that's it.

Here's the function that I found:
USE [EdCat]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date:
10/25/2007 08:21:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ',
@delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

Here's my stored procedure:
USE [EdCat]
GO
/****** Object:  StoredProcedure [dbo].[procRptUserCode]    Script
Date: 10/25/2007 08:23:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[procRptUserCode]
@FromUserCode NVARCHAR(4000) = NULL

WITH RECOMPILE
AS
SET NOCOUNT ON
BEGIN
SELECT     User_Code, User_Code_Name
FROM         tblECUserCode
WHERE     User_Code IN (SELECT Item FROM dbo.Split(@FromUserCode,','))
ORDER BY User_Code

END
SET NOCOUNT OFF

If I hard code it with WHERE User_Code IN ('GS0309', 'GS0310') my
report works.  So I tried to concatenate in some quotes in the
function, but I really don't know what I'm doing so I couldn't get it
to work.  Any help would be greatly appreciated!

Thanks
P.S.
As you can probably tell, I'm a newbie to SSRS and Stored Procedures.

What you can do is to have a string parameter that is passed as a multivalue

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

sometable b inner join charlist_to_table(@MYPARAM,Default)f on b.somefield =
f.str

You can also do this

select * from sometable where somefield in (select str from
charlist_to_table(@MYPARAM,Default))

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

SSRS Multi-Value parameters with Stored Procedures

On Oct 25, 10:04 am, "Bruce L-C  [MVP]" <bruce_lcNOS...@hotmail.com>

Thanks Bruce!  That solved my problem
I had seen this solution presented, but I was missing the part about:

You can also do this
select * from sometable where somefield in (select str from
charlist_to_table(@MYPARAM,Default))

I wasn't how to implement it (because I don't really know what I'm
doing :o)  ! ), so I chose the other solution for my function, which
only worked for numeric parameters.
Thanks again!