Passing an array to SQL Server
Often times i need to pass a variable number of arguments to SQL, and since there's no array support i usually end up doing:
- Adding parameters to T-SQL with default values
- Passing a string with some delimiter and building a table from that
1. Adding parameters to T-SQL with default values is something like:
CREATE PROC GetCustomersById
(
@CustomerId1 INT = NULL
, @CustomerId2 INT = NULL
...
) AS
BEGIN
SELECT Customer.CustomerId
, Customer.Name
, ...
FROM Customer
WHERE Customer.CustomerId IN ( @CustomerId1, @CustomerId2 )
END
There are some variations to the way one can consume the values, like storing them on a temp table or table variable, using COALESCE,... .
For simple stuff this works well but it does not scale as you add more parameters, or you need to pass in collections of related parameters (like structures). It starts to get messier.
2. Passing a string with some delimiter and building a table from that
For this method, we need a way to convert a string into a table. For that i use a function that returns a table variable. That's very convenient because one can use it directly on the queries as well as store it on local table variables.
/*
Used to convert a delimited string into a table
Ex: SELECT * FROM StringArray2Table( 'one,two,three,four,five', ',' )
Notes: not dealing with overflow of elements, and when empty elms are found they
are converted to NULL.
*/
CREATE FUNCTION StringArray2Table
(
@stringArray VARCHAR(MAX) -- delimited string array (max elm size = 255)
, @delimiter CHAR(1) = ',' -- , by default
)
RETURNS @Table TABLE
(
ix int identity(1,1) -- useful to correlate two arrays and
-- preserve order of elements
, elm VARCHAR(255) -- elmts may overflow, not being checked
)
AS
BEGIN
DECLARE @elm VARCHAR(255)
DECLARE @from INT
DECLARE @ix INT
SET @from = 1 -- start from the beginning
SET @ix = CHARINDEX( @delimiter, @stringArray, @ix )
WHILE (@ix > 0) -- while there's a match
BEGIN
SET @elm = SUBSTRING( @stringArray, @from, @ix - @from )
IF ( @ix = @from )
INSERT INTO @Table( elm ) VALUES ( NULL )
ELSE
INSERT INTO @Table( elm ) VALUES ( @elm )
SET @from = @ix + 1
SET @ix = CHARINDEX( @delimiter, @stringArray, @from ) -- find next
END
IF ( @from - 1 <= len( @stringArray ) ) -- last elm
BEGIN
SET @elm = SUBSTRING( @stringArray, @from, 255 )
IF LEN(@elm) > 0
INSERT INTO @Table( elm ) VALUES ( @elm )
ELSE
INSERT INTO @Table( elm ) VALUES ( NULL )
END
RETURN
END
and then one can use it like
CREATE PROC GetCustomersById
(
@CustomerList VARCHAR(MAX)
) AS
BEGIN
SELECT Customer.CustomerId
, Customer.Name
, ...
FROM Customer
INNER JOIN dbo.StringArray2Table( @CustomerList, ',' ) CustArray
ON Customer.CustomerId = CustArray.elm
END
I use some variations of this function, specialized by return type (return int for example), not returning the index, returning empty strings instead of NULL, returning more than one column (useful especially for "dictionary" like structures, e.g. 'Caption:asdasd,Height:2312,Width:123').
When i use this function i always have control over the input data and make sure it does not conflict with the delimiter. Of course, it's easy to come up with a convention to avoid delimiter usage inside the elements (like SQL does for 's for example).
There are other ways to pass in arrays, though i don't use them as often mainly because they are a bit more involved and imply more dependencies/configuration. The other options are xml type/OPENXML, varbinary, using SQLCLR (SQL2K5), etc...