Prior to SQL Server 2008, there was no straightforward way to pass arrays in TSQL. While programming languages such as Perl, C# and Ruby adopted array map functions as the lifeblood of the language, SQL Server’s TSQL stayed behind. Fortunately, the functionality is now present in most SQL Server instances, and can be leveraged for interesting new SQL constructs.
The basic problem solved by TSQL arrays is this: assuming that we need to perform an operation for each item in a predetermined list, how can we pass the list through the memory, rather than by creating a temporary table. In-memory tables are favored over temporary tables due to their reduced load on the database, and their command-specific scope.
The first step is to create a “Type”:
create type List_Varchar16 as TABLE(X VARCHAR(16) NOT NULL PRIMARY KEY);
This will define a type that can later be used to pass parameters to functions. This is the only preparatory step that has global database scope; the other commands can be encompassed within a single SQL procedure. Fortunately, the types can be formatted using the syntax above, so that they will only need to be declared once per database, and can be reused in other instances of commands or stored procedures.
Declaring an array using the Type is fairly straightforward. Here we will declare two arrays:
declare @GOODFOOD List_Varchar16;
INSERT @GOODFOOD VALUES ('PIZZA'),('STEAK'),('MUSHROOMS');
declare @BADFOOD List_Varchar16;
INSERT @BADFOOD VALUES ('LIVER'),('MUSH'),('GRUEL');
This SQL Server syntax allows us to encode multiple insert statements in one line, by creating a comma-separated list of parenthesis-enclosed rows.
Next, the list can be passed either to a stored procedure, or to an inline function. Let’s first declare the inline function:
declare @EATSQL nvarchar(4000)
set @EATSQL = 'insert into BELLY(FOOD_ID,FOOD_TASTE) (select X,@FOOD_TASTE from @ARR)';
Finally, we can call the inline function with the two different sets of parameters:
exec sp_executesql @EATSQL,N'@FOOD_TASTE varchar(10),@arr List_Varchar16 readonly','Delicious',@GOODFOOD
exec sp_executesql @EATSQL,N'@FOOD_TASTE varchar(10),@arr List_Varchar16 readonly','Terrible',@BADFOOD
The key in this statement is to use the “readonly” attribute on the list. This will allow SQL Server to properly parse the array parameter.
In sum, the list technique can be used to make TSQL code easier to read and maintain. For optimum performance, the data should be saved to a global table instead, and accessed using traditional techniques. Although there is a slight performance overhead to TSQL arrays, they provide an elegant way to iterate over an in-memory table without requiring maintenance of an additional database table, or resorting to less elegant temporary tables.
Written by Andrew Palczewski
About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+
