Technical Article

Making a table out of a list

,

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

Rate

1.25 (4)

Share

Share

Rate

1.25 (4)