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.

1. Creating array in stored procedures.
2. Passing and receiving table variables in stored procedures
3. 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

1. Create table in the TempDb system database
2. Enter data into a table (insert)
3. Return data (select)
4. 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

This technique has some limitations. Varchar type variable cannot accept more than 8000 chars, and Text/nText type variable are invalid for local variables declaration. Let’s re view a scenario when XML is used to deliver a list of values to a stored procedure, from which we can CREATE AN ‘ARRAY’ (TV), and then use that Array, in this case using IN operator as a part of a WHERE clause of a SQL Statement.

Create Procedure ShowOrders
@XMLData text
/* TEXT data type allows us to accept a much larger string, compared to Varchar, which is limited to 8000 characters. */AS
Set Nocount ON
Declare @hDoe int
/*number needed for system to recognize an XML document */Declare @tbl table (ProductName varchar(100))
/*declaring a TV (‘Array’) */Exec sp_xml_preparedocument @hDoc Output, @XMLdata
/*Preparing an XML String for further processing */Insert @tbl select XML_Emp.ProductName
From OPENXML(@hdoc,’root/products’)
With Products XML_Emp

Let’s examine the insert block in more detail. We have populated Array, using an XML string. OPENXML and OPENXML WITH statements create ROWSET VIEW, using XML Document. An obtained result can be loaded directly to TV or TT. WITH statement allows using an existing name of the TABLE, or creating a new schema. I would recommend the second option, since it improves readability and maintenance). Additionally, if you are using WHERE clause, the first option may generate an error.

-- Option 1
Insert @tbl 
 select XML_Emp.ProductName
  From OPENXML(@hdoc,’root/products’)
  With Products XML_Emp
-- Option2:
Insert @tbl 
 select ProductName
  From OPENXML(@hdoc,'root/products')
  With (ProductName VARCHAR(20))

Now, upon passing XML rows to TV, we can use an obtained result in the IN clause.

USE NorthWind
CREATE PROC GetRegions_XML 
@empdata text
AS 
DECLARE @hDoc int
DECLARE @tbl TABLE(state VARCHAR(20))
exec sp_xml_preparedocument @hDoc OUTPUT, @empdata   
INSERT @tbl 
SELECT StateName
FROM OPENXML(@hDoc, 'root/States')   
WITH (StateName VARCHAR(20))
EXEC sp_xml_removedocument @hDoc   
SELECT * FROM Suppliers
WHERE Region IN (SELECT * FROM @tbl)
/*
declare @s varchar(100)
set @s = '<root><States StateName = "LA"/>
<States StateName = "MI"/> 
<States StateName = "OR"/></root>'
exec GetRegions_XML @s
*/

Using Cursors

Everyone is aware of the fact that Cursors are memory-hungry. That is why it is recommended to use them as the last resort, when there are no other ways to solve a problem. This is true. It has been a while since I last used a cursor in my T-SQL programming practice. And it is understandable, since there is a much better alternative: using TV, which works great, and not nearly as much resource-hungry.

One of the main usages of Cursor is the functionality that allows to work with each particular single row out of a given result set. While coding a similar functionality via TV-Array, we need to have a row identifier, or an ID for each row. Using Identity property is very convenient for such purposes. Identity property can serve as a unique identifier for each row and will allow imitating Fetch functionality on the row-by-row basis.

Below you can find a comparative table of 2 stored procedures, one using a traditional Cursor, and the other – TV. You may notice that a cursor-based stored procedure contains fewer lines of code. Nevertheless, the activity and resources used by SQL server are much higher compared to using TV. Indeed, declaring Cursor and using Fetch statements are compact and straightforward. In the event of using TV, there is definitely more coding involved, but it allows programmer more control, and saves SQL Server resources and processing time, which is by all means a very important issue in a high-volume multi-user transactional application setting.

-- Transact SQL Cursor
CREATE PROC SQL_Cursor 
AS 
/*Local variables */DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50)
/*create the cursor*/DECLARE MyCursor CURSOR FOR 
SELECT  CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*open the cursor*/OPEN MyCursor
/*get row values*/FETCH MyCursor INTO @CompName, @ContName
/*perform oterations with single row*/PRINT 'T_SQL cursor row  |  ' + @ContName + '  |  ' + @CompName
/*establish loop structure*/WHILE @@FETCH_STATUS = 0
BEGIN
/*get row values*/FETCH MyCursor INTO @CompName, @ContName
/*perform operations with single row*/PRINT 'T_SQL cursor row  |  ' + @ContName + '  |  ' + @CompName
END
/*close the cursor*/CLOSE MyCursor
/*remove the cursor definition*/DEALLOCATE MyCursor
-- Cursor Simulator
CREATE PROC CursorSimulator 
AS 
/*Prepare TABLE variable to take resultset*/DECLARE @tbl TABLE(
          RowID INT IDENTITY(1, 1),
          CompanyName VARCHAR(100),
          ContactName VARCHAR(50))
/*Local variables */DECLARE @ContName VARCHAR(100),
@CompName VARCHAR(50),
@count int, /*create local @@fetch_status*/@iRow int   /*row pointer (index)*//* create array simulator */INSERT @tbl 
SELECT CompanyName, ContactName
FROM Suppliers
WHERE ContactName LIKE 'c%'
/*get array Upper Bound (highest ID number)*/SET @count = @@ROWCOUNT
/*initialize index counter*/SET @iRow = 1
/*establish loop structure*/WHILE @iRow <= @count
BEGIN
/*get row values*/SELECT @ContName = CompanyName, @CompName = ContactName
FROM @tbl
WHERE RowID = @iRow
/*perform operations with single row*/PRINT 'My cursor row  |  ' + @ContName + '  |  ' + @CompName
/*go to next row*/SET @iRow = @iRow + 1
END

Despite of the fact that there may be more ways to solve the problem, methods and techniques that I suggested could can obviously broaden our possibilities in using new programming structures of SQL Server 2000.