David's Blog

Sooner or later, you’ll come across the need to pass “lists” of items into a SQL Server Stored Procedure. Unfortunately, SQL Server doesn’t help us out much here. There are a lot of approaches¹, but most of them seem more “workaround” than “solution” in nature.

When dealing with fixed length data-items, you can make use of the [sequence_generator] function that I showed in an earlier post. Here’s an example function to extract UNIQUEIDENTIFIER values from a VARBINARY(max) input parameter:

CREATE FUNCTION [dbo].[unpivot_uniqueidentifiers](@uniqueidentifiers VARBINARY(max))

      RETURNS TABLE

      WITH SCHEMABINDING

AS RETURN

      SELECT v = CAST(SUBSTRING(@uniqueidentifiers, seq.i, 16) AS UNIQUEIDENTIFIER)

            FROM [dbo].[sequence_generator](DATALENGTH(@uniqueidentifiers) / 16, 1, 16) seq

You can easily adapt the above function for INTEGER, DATETIME and other fixed-length data-types. Turning the “list” into a table is straightforward:

DECLARE @input VARBINARY(max)

–Build a “list” of (up to 100) UNIQUEIDENTIFIERs

SELECT @input = ISNULL(@input, CAST(NEWID() AS BINARY(16))) + CAST(NEWID() AS BINARY(16))

      FROM [dbo].[sequence_generator](RAND() * 100, 0, 1)

–”unpivot” into a table

SELECT ui.v
      FROM [dbo].[unpivot_uniqueidentifiers](@input) ui

¹ See this article by Erland Sommarskog for a detailed discussion of many approaches (including a variation of the method discussed here) to representing lists in SQL Server.