SQL Stored Procedure Array Parameters Dilemma, Solved with CLR

12/16/2009 2:55:00 AM

If you work actively with SQL Server and do most of the data operations using stored procedures, then I bet that at least once, you wanted to pass an array of values as a parameter to a stored procedure (or function). Just like you, I had the same situation. I’ve seen many approaches to this problem and all of them has their pros and cons. I’ve implemented a solution using a CLR Stored Procedure, that I find quite practical and well performing.

What to do:

First, create an SQL CLR Stored Procedure. For detail on this, check my post Writing a CLR Stored Procedure on SQL Server.

Use this code as your CLR Stored Procedure:

    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub spArrayToTable(ByVal CSVs As SqlString, ByVal ReturnDataType As SqlString)

        Dim sFieldName As String = "Value"

        Dim record As SqlDataRecord

        Select Case ReturnDataType

            Case "int"
                record = New SqlDataRecord(New SqlMetaData(sFieldName, SqlDbType.Int))

            Case "bigint"
                record = New SqlDataRecord(New SqlMetaData(sFieldName, SqlDbType.BigInt))

            Case "float"
                record = New SqlDataRecord(New SqlMetaData(sFieldName, SqlDbType.Float))

            Case "varchar"
                record = New SqlDataRecord(New SqlMetaData(sFieldName, SqlDbType.VarChar, 8000))

            Case "nvarchar"
                record = New SqlDataRecord(New SqlMetaData(sFieldName, SqlDbType.NVarChar, 4000))

            Case Else
                record = New SqlDataRecord(New SqlMetaData(sFieldName, SqlDbType.VarChar, 8000))

        End Select

        SqlContext.Pipe.SendResultsStart(record)

        For Each sValue As String In CSVs.ToString.Split(CChar(","))

            sValue = sValue.Trim(CChar("'"))

            Select Case ReturnDataType

                Case "int"
                    record.SetInt32(0, CType(sValue, Int32))

                Case "bigint"
                    record.SetInt64(0, CType(sValue, Int64))

                Case "float"
                    record.SetFloat(0, CType(sValue, Single))

                Case "varchar", "nvarchar"
                    record.SetString(0, sValue)

                Case Else
                    record.SetString(0, sValue)

            End Select

            SqlContext.Pipe.SendResultsRow(record)
        Next

        SqlContext.Pipe.SendResultsEnd()

    End Sub

Deploy it and test it.

This procedure receives two parameters, a comma separated values string (the array you want to convert to a table) and a return data type identifier string.

Practical Example:

Create a simple scenario. A Customer table with Name and Lastname.

IF EXISTS (SELECT object_id FROM sys.tables WHERE Name = 'Customer')
      DROP TABLE Customer
GO

CREATE TABLE Customer
(
       CustomerID int identity(1,1)
      ,Name varchar(50)
      ,LastName varchar(50)
)

INSERT INTO Customer
VALUES
 ('Carlos','Bercero')
,('José','Rivera')
,('John','Doe')


SELECT * FROM Customer

This is how it should look like:

Now, create a Stored Procedure to retrieve the list of customers by their IDs:

CREATE PROC spGetCustomersByIDs
      @CustomersIDs varchar(8000)
      --Receive Comma Separated Values
AS

DECLARE @CustomersIDsTable TABLE (CustomerID int)
--Temporay table for the array values

INSERT INTO @CustomersIDsTable
      EXEC spArrayToTable @CustomersIDs, 'int'
      --Fill the temporary table with the array values

SELECT
      *
FROM
      Customer
WHERE
      CustomerID IN (SELECT CustomerID FROM @CustomersIDsTable)
                     --Use the values in the temp table as the filter

Test the stored procedure:

This is extremely useful when using multi-valued parameters in Reporting Services.

Fell free to leave a question or comment.

You liked this post? Share it…