The UDF will allow you to define a multi-valued parameter in reporting services (there are lots of other uses as well), and pass that parameter into a stored procedure.
Within the stored procedure you would use it like this:
(@strMy_User_ID_List is the passed parameter)
select *
from tblUsers
where numUser_ID in
(
select *
from dbo.dbo.fnSYS_Split_Comma_Separated_List_Of_Integers_To_Integer_Table(@strMy_User_ID_List)
)
FUNCTION fnSYS_Split_Comma_Separated_List_Of_Strings_To_String_Table ( @strData varchar(8000) ) RETURNS @output TABLE (strData varchar(8000)) AS BEGIN declare @intPosition int declare @intStart int declare @intEnd int declare @strPart varchar(8000) set @strData = replace(@strData,' ,',',') set @strData = replace(@strData,', ',',') if (charindex(',', @strData) = 0) --if only one value append a , so that it still gets parsed set @strData = @strData + ',' set @intPosition = charindex(',', @strData) insert into @output values (ltrim(rtrim(substring(@strData, 0, @intPosition)))) if charindex(',',@strData) > 0 begin while ((charindex(',', @strData, @intPosition)) <> 0) begin set @intStart = charindex(',', @strData, @intPosition) set @intEnd = charindex(',', @strData, @intStart+1) if (@intEnd = 0) set @intEnd = len(@strData) - @intStart set @strPart = ltrim(rtrim(substring(@strData, @intStart+1, abs(@intEnd - @intStart-1)))) insert into @output values (@strPart) set @intPosition = charindex(',', @strData, @intPosition) + 1 end end return END --Here is the int version FUNCTION dbo.fnSYS_Split_Comma_Separated_List_Of_Integers_To_Integer_Table ( @strData varchar(8000) ) RETURNS @output TABLE (intData int) AS BEGIN declare @intPosition int declare @intStart int declare @intEnd int declare @strPart varchar(8000) if charindex(',', @strData) = 0 begin insert @output values (convert(int,ltrim(rtrim(@strData)))) end else begin set @strData = replace(@strData,' ,',',') set @strData = replace(@strData,', ',',') set @intPosition = charindex(',', @strData) insert @output values (convert(int,ltrim(rtrim(substring(@strData, 0, @intPosition))))) if charindex(',',@strData) > 0 begin while ((charindex(',', @strData, @intPosition)) <> 0) begin set @intStart = charindex(',', @strData, @intPosition) set @intEnd = charindex(',', @strData, @intStart+1) if (@intEnd = 0) set @intEnd = 500 set @strPart = ltrim(rtrim(substring(@strData, @intStart+1, abs(@intEnd - @intStart-1)))) insert into @output values (convert(int,@strPart)) set @intPosition = charindex(',', @strData, @intPosition) + 1 end end end return END
