If you use SQL Server for any considerable amount of time, you’ll likely encounter a situation in which it would be very useful to pass an array of values into a function or procedure and handle data based on that array of values.

In SQL Server 2008, you can pass tables as parameters. In SQL Server 2005, you can pass around values in such a way using the native XML data type. You can even accomplish it in SQL Server 2000 by using OPENXML and using XML data similar to the way you’d use it in SQL Server 2005.

Many DBAs using SQL Server 2000 or SQL Server 2005 are still not very comfortable parsing XML data to get an array of values (and no one is using SQL Server 2008 yet). I’ll show you an approach I use for converting arrays of string values to a dataset in SQL Server 2000 and later.

When to use this approach

I’m not a huge fan of this approach of passing parameters. I feel that SQL Server has more than adequate native constructs for handling such situations. However, I have fielded literally hundreds of questions regarding this scenario in various SQL Server forums, so it is a generically usable approach to passing an array of data.

An Array List

For my example, the Array List is a string of values separated by a common delimiter. This a valid array list for the example:

DECLARE @ArrayList VARCHAR(MAX)
SET @ArrayList = 'tim,zach,chris,wendi,brad'

The goal is to take that string of values and pivot them so that all of the values occur in the same row of data; then you can join the data to another table, etc. To do this, I will pass this list to a table-valued function, and the function is going to take care of the pivoting work for me. Below is the code to my pivot function:

CREATE FUNCTION [dbo].[udf_PivotParameters]

    (


      @ParamaterList VARCHAR(MAX),


      @Delimiter CHAR(1)


    )


RETURNS @ReturnList TABLE


    (


      FieldValue VARCHAR(MAX)


    )


AS BEGIN


    DECLARE @ArrayList TABLE


        (


          FieldValue VARCHAR(MAX)


        )


    DECLARE @Value VARCHAR(MAX)


    DECLARE @CurrentPosition INT


 


    SET @ParamaterList = LTRIM(RTRIM(@ParamaterList))


        + CASE WHEN RIGHT(@ParamaterList, 1) = @Delimiter THEN ''


               ELSE @Delimiter


          END


    SET @CurrentPosition = ISNULL(CHARINDEX(@Delimiter, @ParamaterList, 1), 0)


    IF @CurrentPosition = 0


        INSERT  INTO @ArrayList ( FieldValue )


                SELECT  @ParamaterList


    ELSE


        BEGIN


            WHILE @CurrentPosition > 0


                BEGIN


                    SET @Value = LTRIM(RTRIM(LEFT(@ParamaterList,


                                                  @CurrentPosition - 1))) --make sure a value exists between the delimiters


                    IF LEN(@ParamaterList) > 0


                        AND @CurrentPosition <= LEN(@ParamaterList)


                        BEGIN


                            INSERT  INTO @ArrayList ( FieldValue )


                                    SELECT  @Value


                        END


                    SET @ParamaterList = SUBSTRING(@ParamaterList,


                                                   @CurrentPosition


                                                   + LEN(@Delimiter),


                                                   LEN(@ParamaterList))


                    SET @CurrentPosition = CHARINDEX(@Delimiter,


                                                     @ParamaterList, 1)


                END


        END


    INSERT  @ReturnList ( FieldValue )


            SELECT  FieldValue


            FROM    @ArrayList


    RETURN

   END

The function accepts the list of values, along with the delimiting character to separate the list. Then the function loops through the array list, inserting everything between the delimiting characters into a table variable, which is eventually returned as the output from the function. Because this is a table-valued function, you need to return the values from a SELECT statement. This code snippet shows you how:

DECLARE @ArrayList VARCHAR(MAX)
SET @ArrayList = 'tim,zach,chris,wendi,brad'
SELECT * FROM dbo.udf_PivotParameters(@ArrayList, ',')

Performance

I suggest that you use this approach sparingly if possible because the performance is going to drastically degrade as the values in the list increases.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.