Multi value string parameters
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!!
[540 byte] By [
dipika] at [2007-12-16]
May be this:="'" + Join(Parameters!SelectedColor.Value, "', '")) + "'"Or you may be able to pass the SelectedColor parameter to a custom function which returns it formatted as required by the stored proc.
Hello Friends
I am also facing same problem can u help me
ALTER
PROCEDURE [dbo].[SampleMulti]( @val1
nvarchar(200), @val2
nvarchar(200)) AS
SET
NOCOUNT ON; DECLARE
@Str nvarchar(200) SET @Str=' WHERE'
IF(@val1 IS NULL AND @val2 IS NULL) EXEC('SELECT val1,val2,val3 FROM Tablename') ELSE
BEGIN
IF(@val1 IS NOT NULL)AND (@val1<>'') BEGIN SET @Str = @Str + ' AND dtp IN' + '(' + @val1 + ')' *--The problem is its not taking that string value ('val1','val2','val3') How to convert here
END
END EXEC('SELECT val1,val2,val3 FROM Tablename' + @str) END
plz can any body help i really struck Here
You might have to quote the values properly?
Yeah, not able to pass multi-value parameter to a SP really sucks
because you can do it in Visual Studio query window, but I'd rather not have the SP code in there instead of backend
We have to resort to using this logic to get the values into a temp table first, then use JOIN or IN later in other query. I hope this solves the issue
SELECT
id, description
FROM TABLE (NOLOCK)
WHERE ISNULL(@str,'ALL') = 'ALL' -- takes care of the default NULL
OR CHARINDEX(',' + CONVERT(VARCHAR, id) + ',', ',' + @str + ',') > 0
