March 19, 2008 at 9:42 am
how do I create the WHERE clause in a sproc to handle a parameter from reporting services 2005 that can send multiple values?
I am trying
WHERE jm.SectorNum IN (@strSectorNum) but this does not seem to be working
Thanks
Dean
March 19, 2008 at 10:33 am
I have done this for a recent report. I first created a function called dbo.fn_MultiSelectStr that is passed the parameter value. It then adds quotes around each selected value, i.e. looks for the commas and adds single quotes before and after. The end value should then look something like the following:
'value1','value2','value3'
Your sp will have to create dynamic sql to execute, i.e.
@sql = 'select * from table1 where '
set @STR = dbo.fn_MultiSelectStr(@param1)
set @sql = @sql + 'field1 in (' + @STR + ')'
exec (@sql)
Hope this makes sense
Adam
April 9, 2008 at 3:02 pm
If you want to avoid dynamic sql, you can create a function that parses out the comma-separated values and puts them in a table.
CREATE FUNCTION [dbo].[fn_ParseComma] (@STRING nvarchar(max))
RETURNS @Tmp TABLE
(mystr nvarchar(150))
AS
BEGIN
DECLARE @Count int
SELECT @Count = 0
WHILE (SELECT CHARINDEX(',', @STRING)) > 0
BEGIN
INSERT @Tmp SELECT LTRIM(RTRIM(SUBSTRING(@STRING, 1, CHARINDEX(',', @STRING)-1)))
SELECT @STRING = LTRIM(RTRIM(SUBSTRING(@STRING, CHARINDEX(',', @STRING)+1, LEN(@STRING))))
END
INSERT @Tmp SELECT LTRIM(RTRIM(@STRING))
RETURN
END
For example – for a field called OperatingUnit (parameter is @OU):
OperatingUnit IN (SELECT mystr FROM dbo.fn_ParseComma (@OU))
If the list is going to be quite long, we sometimes create a temp table to hold the parameter values, and then join to the temp table.
INSERT INTO #OU
SELECT mystr as OU FROM dbo.fn_ParseComma (@OU)
A co-worker of mine wrote this, and we use it all the time for our multi-value parameters. Hope this helps!
-Marianne
April 11, 2008 at 3:03 am
where in(@Code) it works
but where in('"+parameters!Code.Value+"') I am writing but it doesnt work why??
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply