SQL Stored Procedure Array Parameters Dilemma, Solved with CLR

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.

image

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:

image

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:

image

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…

3 comments
Posted by Alan Koo on 12/16/2009 6:16:30 PM
Good article!

Another option could be using PatIndex or CharIndex, something like this:

DECLARE @p VARCHAR(50)
SET @p = 'BE-2908,CA-5965,CB-2903,DC-8732,FH-2981'

SELECT ProductID, Name, ProductNumber
FROM Production.Product
WHERE CHARINDEX( ',' + ProductNumber + ',', ',' + @p + ',' ) > 0 ;

Regards,

______________________________________________________________
http://alan-koo.blogspot.com :)
Posted by Carlos Bercero on 12/18/2009 2:28:06 PM
That is right Alan. Although if the value list is too long, that string index search could get a little slow.

Thanks for the comment.
Posted by Guillermo Caicedo on 5/27/2010 6:55:40 PM
For those who prefer C#

[Microsoft.SqlServer.Server.SqlProcedure]
public static void spArraytoTable(SqlString CSVs, SqlString ReturnDataType)
{
string sFieldName = "Value";
SqlDataRecord record;
string sValue2, DataType;
char[] arr = new char[] {','};

DataType = ReturnDataType.ToString();

switch (DataType)
{
case "int":
record = new SqlDataRecord(new SqlMetaData(sFieldName, SqlDbType.Int));
break;
case "bigint":
record = new SqlDataRecord(new SqlMetaData(sFieldName, SqlDbType.BigInt));
break;
case "float":
record = new SqlDataRecord(new SqlMetaData(sFieldName, SqlDbType.Float));
break;
case "varchar":
record = new SqlDataRecord(new SqlMetaData(sFieldName, SqlDbType.VarChar, 8000));
break;
case "nvarchar":
record = new SqlDataRecord(new SqlMetaData(sFieldName, SqlDbType.NVarChar, 4000));
break;
default:
record = new SqlDataRecord(new SqlMetaData(sFieldName, SqlDbType.VarChar, 8000));
break;
}

SqlContext.Pipe.SendResultsStart(record);

foreach(string sValue in CSVs.ToString().Split(','))
{
sValue2 = sValue.Trim(arr);

switch (DataType)
{
case "int":
record.SetInt32(0, Convert.ToInt32(sValue2));
break;
case "bigint":
record.SetInt64(0, Convert.ToInt64(sValue2));
break;
case "float":
record.SetFloat(0, Convert.ToSingle(sValue2));
break;
case "varchar":
record.SetString(0, sValue2);
break;
case "nvarchar":
record.SetString(0, sValue2);
break;
default:
record.SetString(0, sValue2);
break;
}

SqlContext.Pipe.SendResultsRow(record);
}

SqlContext.Pipe.SendResultsEnd();
}
Leave a comment
Display Name:  


Email:    


Comment:  



Domain Registrations starting at $9.69
Powered by Carlos Bercero Blog Platform © 2009