Multi value string parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deepu_t@hotmail.com - 01 Aug 2005 06:25 GMT

Hi all

I have a multi value drop down list and their values are a string data
type.

Label Value
Blue BL
Pink PK

If I select more than one values the parameter value is sent as 'BL,
PK'

The stored procedure has a where clause

WHERE theColor IN (@SelectedColor)

This does'nt return the expected results from the stored procedure.
How do we make the reporting services pass 'BL','PK' instead of 'BL,
PK' to the stored procedure?
How do I make this work??

Thanks heaps!!

Reply to this Message

goodman93 - 01 Aug 2005 13:31 GMT

Try creating this function:

CREATE FUNCTION ParamsToTable( @delimString varchar(255) )
RETURNS @paramtable TABLE ( Id int ) AS

BEGIN
DECLARE @len int,@index int,@nextindex int
SET @len = DATALENGTH(@delimString)
SET @index = 0
SET @nextindex = 0

WHILE (@len > @index )
BEGIN
SET @nextindex = CHARINDEX(';', @delimString, @index)
if (@nextindex = 0 ) SET @nextindex = @len + 2
INSERT @paramtable
SELECT SUBSTRING( @delimString, @index, @nextindex - @index )
SET @index = @nextindex + 1
END
RETURN
END
GO

And then use this in a join statment.

Select * from table
join ParamsToTable(',', @SelectedColor)

> Hi all
>
[quoted text clipped - 18 lines]
>
> Thanks heaps!!

Reply to this Message

deepu_t@hotmail.com - 02 Aug 2005 00:21 GMT

Thanks for your reply.

Where do i write the statements

> Select * from table
> join ParamsToTable(',', @SelectedColor)

Would this be in the stored procedure?
Thanks again!!

> Try creating this function:
>
[quoted text clipped - 46 lines]
> >
> > Thanks heaps!!

Reply to this Message

Jack Truneh - 30 Nov 2005 19:16 GMT

Help!!! ,this works, but if i am accepting multivalue parameters from Asp.
net UI ,parameters are correctly initialized with the values(string[]) but
the report does not filer it on the values passed.

Reply to this Message