How do I simulate an array inside a stored procedure?

Passing in an array 
 
Often you will want to pass an array to a stored procedure and have the procedure loop through and process each element in the array. Unfortunately, T-SQL does not have an array datatype. 
 
Erland Sommarskog recently wrote an article entitled Arrays and Lists in SQL Server which is really worth a read. Ken Henderson's latest book, Amazon.com: The Guru's Guide to SQL Serv..., has a section on dealing with arrays in T-SQL, with extended stored procedures (accompanied by source code) on the CD-Rom. His method actually adds array support to T-SQL, rather than work around its absence. 
 
While you're waiting for the book to arrive, or for Erland's article to print out <G>, what you can do instead, is pass in a list of comma-delimited strings and parse it out, inserting it into a temp table of your choice. For example: 
 

CREATE PROCEDURE dbo.FAQ_IntListToTable      @cslist VARCHAR(8000),      @tablename SYSNAME AS  BEGIN      DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)        WHILE @cslist <> ''      BEGIN          SET @spot = CHARINDEX(',', @cslist)          IF @spot>0              BEGIN                  SET @str = CAST(LEFT(@cslist, @spot-1) AS INT)                  SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)              END          ELSE              BEGIN                  SET @str = CAST(@cslist AS INT)                  SET @cslist = ''              END          SET @sql = 'INSERT INTO '+@tablename+' VALUES('+CONVERT(VARCHAR(100),@str)+')'          EXEC(@sql)      END  END

 
Then you could use this procedure, for example, to do something like this: 
 

CREATE TABLE #vals (id INT)  EXEC FAQ_IntListToTable '1,2,3,5,7,8','#vals'  SELECT id FROM #vals  DROP TABLE #vals

 
Only minor changes if you are using a comma-separated list of VARCHAR values: 
 

CREATE PROCEDURE dbo.FAQ_StrListToTable       @cslist VARCHAR(8000),       @tablename SYSNAME AS   BEGIN       DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)             WHILE @cslist <> ''       BEGIN           SET @spot = CHARINDEX(',', @cslist)           IF @spot>0               BEGIN                   SET @str = LEFT(@cslist, @spot-1)                  SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)               END           ELSE               BEGIN                   SET @str = @cslist                  SET @cslist = ''               END           SET @sql = 'INSERT INTO '+@tablename+' VALUES('''+CONVERT(VARCHAR(100),@str)+''')'           EXEC(@sql)       END   END   GO

 
Then you call it like this: 
 

CREATE TABLE #vals (firstname VARCHAR(255))   EXEC FAQ_ListToTable 'jim,bob,frank,joe,tom','#vals'   SELECT firstname FROM #vals   DROP TABLE #vals

 
 
This eliminates the need, for example, to limit your lists to 4,000 or 2,667 characters when they're used multiple times in a single dynamic SQL statement. Instead, you could just join against the #temp table (and use up to 8,000 characters). 
 
Using a table-valued function 
 
Sometimes you have a list stored in a table, e.g. 
 

CREATE TABLE dbo.Properties  (      pk INT PRIMARY KEY,      emailList VARCHAR(8000)  )  GO    INSERT dbo.Properties SELECT 1, 'a@b.com,c@d.com,e@f.com'  GO

 
Now, you want those values back in a resultset like follows: 
 

Item  -------  a@b.com  c@d.com  e@f.com

 
Doing so from the table directly would be pretty ugly. You could use an interim temp table and a procedure such as the above, or you could use a table-valued UDF in SQL Server 2000. 
 

CREATE FUNCTION dbo.FAQ_CommaSeparatedListToSingleColumn  (      @cslist VARCHAR(8000)  )  RETURNS @t TABLE  (      Item VARCHAR(64)  )  BEGIN       DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000)             WHILE @cslist <> ''       BEGIN           SET @spot = CHARINDEX(',', @cslist)           IF @spot>0               BEGIN                   SET @str = LEFT(@cslist, @spot-1)                   SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot)               END           ELSE               BEGIN                   SET @str = @cslist                   SET @cslist = ''               END           INSERT @t SELECT @str      END       RETURN  END  GO

 
Now, it's as simple as: 
 

DECLARE @emailList VARCHAR(8000)    SELECT @emailList = emailList      FROM dbo.Properties      WHERE pk = 1    SELECT EmailList = Item      FROM dbo.FAQ_CommaSeparatedListToSingleColumn(@emailList)      ORDER BY 1

 
And here's a trick from Anith Sen's, well, bag of tricks. It uses a numbers table (see Article #2516). 
 

DECLARE @emailList VARCHAR(8000)     SELECT @emailList = emailList      FROM dbo.Properties       WHERE pk = 1    SELECT      Email = SUBSTRING(@emailList, n, CHARINDEX(',', @emailList + ',', n ) - n)  FROM      (          SELECT n          FROM dbo.numbers      ) numbers  WHERE      SUBSTRING( ',' + @emailList, n, 1 ) = ','

 
You could put that functionality into a table-valued function as well, but I'll leave that as an exercise to the reader. 
 
Generating an array from a string 
 
If you are looking to replicate the Split() function found in VB, VBScript and JavaScript, look no further than fn_Split().