On a number of occasions that I have regretted the fact that there is no such thing as Array in Microsoft SQL Server's Transact-SQL. Lots of other SQL programmers would mention the same thing. The array is one of the most common and highly used programming structures. Indeed, T-SQL does not provide for the fully functional array structure. However, SQL 2000 introduced the new feature called a variable of type table, which allows for mimicking an array and/or using it instead of SQL Server cursor.
In this article I will elaborate on the way I am using this ‘array’ in stored procedures which has drastically increased the functional capabilities of SQL programming.
I have broken my techniques down into three sections.
Creating array in stored procedures.
Passing and receiving table variables in stored procedures
Using ‘Array’ in place of cursors.
My techniques are primarily based on using table type variables, which have become available starting with SQL Server 2000. If you are still working in SQL Server versions 6.5 or 7, sections 1 and 3 of this article may still be useful to you. However, you will have to use a Temporary Table (declared with # sign) instead of a table variable.
Section 2 is dedicated to using XML, which is why it may not be useful in any development environment other than SQL 2000. TT (temporary tables) can be used in version 2000 as well, but using temporary table you are increasing disk activity since TV (Table Variable) is created in memory as opposed to Temp tables (created in TempDb). In other words, stored procedures incorporating temp tables are slower than the ones using TVs. In order for SQL Server to create TT, the system must
Create table in the TempDb system database
Enter data into a table (insert)
Return data (select)
Remove table (Drop)
A similar process is required for TV, but all operations are conducted 100% in memory. I have recently experimented with these alternatives, and found out that the exact same stored procedure using TV is executed twice as fast compared to another stored procedure which used temp tables.
To create a simulated array (TV), we need a structure similar to array as it is known in most programming environments. Declaration of a table variable looks like this:
Declare @tbl table ( )
The above statement creates an equivalent of an Array. Type of this ‘Array’ depends on the data type of column. For example,
Declare @tbl table (FirstName varchar(20), LastName varchar(20))
Creates a string type array. Other data types can be used as well, such as int, float, datetime, money, etc. One should remember that SQL server does not have an implicit datatype conversion functionality (unlike VB and some other development environments). It means that data which you intend to use to populate an array, must be formatted according to the specification of each declared data type. Such functions as Convert or Cast can be used to format data appropriately.
In order to have a ‘complete’ array, with its full functionality, we lack an Index. I use an additional column for this, declared as Int data type, using an Identity property. Identity definition requires 2 arguments: Seed and Increment. Thus, we can create a 0 (zero) or 1-based array. The following array enjoys its full functionality:
Declare @tbl table (RowId int identity(1,1), ItemName varchar(100))
Now we can populate this array (using Northwind database) as follows:
Insert @tbl
Select ProductName
FROM Products
WHERE ProductName like ‘M%’
Select * from @tbl
GO
-- Result:
RowId ItemName
----- ----------------
1 Mishi Kobe Niku
2 Mascarpone Fabioli
3 Maxilaku
4 Manjimup Dried Apples
5 Mozzarella di Giovanni
[In this example you could see that column RowID is provide index functionality, ItemName stores actual values.]
Now it is clear that TV is a programming structure equivalent to ARRAY.
We have demonstrated creating an array programmatically in SQL Server. Our major task is to use an array in stored procedures, including passing TV as an argument. Along with that, I would like to elaborate on one more possibility to pass an entire list of values passed to a stored procedure argument and used within the procedure. I use XML for such purposes. This structure has become available to us starting from SQL Server version 2000 only. When is an operation like this required? For instance, if you intend to pass more than one value to a procedure but not sure how many total values will be there. Consider the query:
Select * from customers WHERE Region IN (‘OR’,’CA’,’WA’)
IN Operator in WHERE clause can use a variable number of values, and return a result set correspondingly to this multiple criteria.
It can be a challenge to pass such a list of values to a stored procedure. In earlier times I had been using so called “composite queries”, or “dynamic SQL”. It includes putting together a string to be compiled and executed using EXECUTE statement. For example:
CREATE PROCEDURE CustomerByRegion
@List varchar(100)
AS
declare @sql varchar(1000)
set @sql = 'select * from Customers where Region IN (' + @List + ')'
execute (@sql)
-- call procedure
declare @List varchar(100)
set @List = '''OR'', ''CA'', ''WA'''
execute CustomerByRegion @List