id="info"

Data Management

Convert a string array to tabular data in SQL Server

Many DBAs using SQL Server 2000 or SQL Server 2005 are still not very comfortable parsing XML data to get an array of values. Check out this approach for converting arrays of string values to a dataset in SQL Server 2000 and later.

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.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

Editor's Picks