|
Home > Archive > MS SQL Server Reporting Services > November 2005 > Multi value string parameters
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Multi value string parameters
|
|
| deepu_t@hotmail.com 2005-08-01, 3:25 am |
| 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!!
| |
| goodman93 2005-08-01, 7:25 am |
| 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(@delimStr
ing)
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)
<deepu_t@hotmail.com> wrote in message
news:1122873928.521881.234610@o13g2000cwo.googlegroups.com...
> 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!!
>
| |
| deepu_t@hotmail.com 2005-08-01, 8:24 pm |
| 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!!
goodman93 wrote:[color=darkred
]
> 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(@delimStr
ing)
> 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)
>
>
>
> <deepu_t@hotmail.com> wrote in message
> news:1122873928.521881.234610@o13g2000cwo.googlegroups.com...
| |
| Jack Truneh 2005-11-30, 1:23 pm |
| 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.
|
|
|
|
|