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

Answer 1

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. :)

Answer 2

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.

Answer 3

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 4

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