ARRAY In SQL Server 2000

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.

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

Article by Alex Grinberg, more