Array-like access to variables in T-SQL

Question

In my stored procedure I have multiple similar variables @V1, @V2 ... @V20 (let's say 20 of them) FETCHED from a record. How would I use dynamic SQL to make 20 calls to another stored procedure using those variables as parameters?

Of course @V[i] syntax is incorrect, but it expresses the intent

 fetch next from maincursor into @status, @V1, @V2, ...
 while @i<21
 begin
 -- ??? execute sp_executesql 'SecondSP', '@myParam int', @myParam=@V[i]
 -- or 
 -- ??? execute SecondSP @V[i]
 set @i = @i+1
 end

• Tomalak: A bit of context would be good. Why are you trying to do this?

Answer 1

As others have said, set up a temporary table, insert the values that you need into it. Then "iterate" through it executing the necessary SQL from those values. This will allow you to have 0 to MANY values to be executed, so you don't have to set up a variable for each.

The following is a complete sample of how you may go about doing that without cursors.

SET NOCOUNT ON
DECLARE @dict TABLE (
 id INT IDENTITY(1,1), -- a unique identity column for reference later
 value VARCHAR(50), -- your parameter value to be passed into the procedure
 executed BIT -- BIT to mark a record as being executed later
 )
-- INSERT YOUR VALUES INTO @dict HERE
-- Set executed to 0 (so that the execution process will pick it up later)
-- This may be a SELECT statement into another table in your database to load the values into @dict
INSERT @dict
SELECT 'V1Value', 0 UNION ALL
SELECT 'V2Value', 0
DECLARE @currentid INT
DECLARE @currentvalue VARCHAR(50)
WHILE EXISTS(SELECT * FROM @dict WHERE executed = 0)
BEGIN
 -- Get the next record to execute
 SELECT 
 TOP 1 @currentid = id 
 FROM @dict 
 WHERE executed = 0
 -- Get the parameter value
 SELECT @currentvalue = value
 FROM @dict
 WHERE id = @currentid
 -- EXECUTE THE SQL HERE
 --sp_executesql 'SecondSP', '@myParam int', @myParam = 
 PRINT 'SecondSP ' + '@myParam int ' + '@myParam = ' + @currentvalue
 -- Mark record as having been executed
 UPDATE d
 SET executed = 1
 FROM @dict d
 WHERE id = @currentid
END

Answer 2

Use a #TempTable

if you are at SQL Server 2005 you can create a #TempTable in the parent stored procedure, and it is available in the child stored procedure that it calls.

CREATE TABLE #TempTable
(col1 datatype
,col2 datatype
,col3 datatype
)
INSERT INTO #TempTable
 (col1, col2, col3)
 SELECT 
 col1, col2, col3
 FROM ...
EXEC @ReturnCode=YourOtherProcedure

within the other procedure, you have access to #TempTable to select, delete, etc... make that child procedure work on a set of data not on one element at a time

remember, in SQL, loops suck performance away!

Answer 3

Why not just use the table variable instead, and then just loop through the table getting each value.

Basically treat each row in a table as your array cell, with a table that has one column.

Just a thought. :)

• Dmitry Duginov: Performance is important, so I would like to avoid extra loops, inserts and fetches whenever possible. If not possible at all I would rather resort to long, ugly CASE statement :(
• James Black: A table variable is quick though, as it is all in memory. It is a replacement of using a temporary table, but you have access to the table functions.

Answer 4

This seems like an odd request - will you always have a fixed set of variables? What if the number changes from 20 to 21, and so on, are you constantly going to have to be declaring new variables?

Is it possible, instead of retrieving the values into separate variables, to return them each as individual rows and just loop through them in a cursor?

If not, and you have to use the individual variables as explained, here's one solution:

declare @V1 nvarchar(100)
set @V1 = 'hi'
declare @V2 nvarchar(100)
set @V2 = 'bye'
declare @V3 nvarchar(100)
set @V3 = 'test3'
declare @V4 nvarchar(100)
set @V4 = 'test4'
declare @V5 nvarchar(100)
set @V5 = 'end'
declare aCursor cursor for
select @V1 
union select @V2 union select @V3
union select @V4 union select @V5
open aCursor
declare @V nvarchar(100)
fetch next from aCursor into @V
while @@FETCH_STATUS = 0
begin
 exec TestParam @V
 fetch next from aCursor into @V
end
close aCursor
deallocate aCursor

I don't really like this solution, it seems messy and unscalable. Also, as a side note - the way you phrased your question seems to be asking if there are arrays in T-SQL. By default there aren't, although a quick search on google can point you in the direction of workarounds for this if you absolutely need them.

• Dmitry Duginov: 1. The number of variables would not change; they are columns in the database table with pretty stable structure 2. The code is simplified of course. In reality there are other variables that play role in the procedure logic, so moving those Vs into another temp table is not a good idea 3. I'm aware that there are no arrays in T-SQL 4. This solution is really messy, I agree
• Dan Fuller: I honestly can't see why you're approaching the data in this way - having variables named V1-VX just screams bad practice. Why isn't the data in rows in a table as opposed to columns? It seems that if you're applying the exact same logic to each column that the table may be designed incorrectly.
• KM.: @Dan Fuller said "I honestly can't see why you're approaching the data in this way", because it seems easier to put data in columns and work with than, then in rows. Some people don't think is sets, and always loop, OP is an example, the design flaws show this too, give up and move on