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…