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