For example,
I have employee parameter in the Report, I am selecting the multiple employee in the
value field, in this scenario does not work normal code. In this situation, you
can use the below two methods used to achieve the multiple parameter value
passing in SSRS Report.
1 JOIN (Parameters!EmpId.Value,”, “) – this method assigns the parameter-value
expression area.
2.Create Function for the storing in a temp table using a function
Sample
code for Delimiter Split function code
CREATE FUNCTION
[dbo].[fn_StringToTable]
(
@String VARCHAR(MAX),
/* input string - delimited data returned from multi-value paramater*/
@Delimiter
CHAR(1), /* delimiter */
@TrimSpace
BIT /* remove leading and trailing
whitespaces */
)
RETURNS @Table
TABLE
(
[Val] VARCHAR(4000)
)
AS
BEGIN
DECLARE @Val VARCHAR(4000)
WHILE LEN(@String)
> 0
BEGIN
SET @Val
= LEFT(@String, ISNULL(NULLIF(CHARINDEX(@Delimiter, @String) - 1, -1), LEN(@String)))
SET @String
= SUBSTRING(@String, ISNULL(NULLIF(CHARINDEX(@Delimiter, @String), 0), LEN(@String))
+ 1, LEN(@String))
IF @TrimSpace
= 1 Set @Val = LTRIM(RTRIM(@Val))
INSERT INTO
@Table ( [Val] ) VALUES ( @Val )
END
RETURN
END
GO
Then our
stored procedure accessing the function the below code.
ALTER PROCEDURE
pcget_Proc(
@Employee
varchar(50)
)
AS
BEGIN
SELECT *
FROM Employee
WHERE LTRIM(RTRIM(EmpId))
IN (SELECT [Val] FROM fn_StringToTable(@Employee, ',', 1))
END
