Accessing a SQL Server Table Variable in a Different Scope

Introduction

Is it possible to access a table variable in a scope other than which it is defined?

The SQL Server documentation suggests the answer is no:

A table variable behaves like a local variable. It has a well-defined scope. This variable can be used in the function, stored procedure, or batch in which it’s declared.

Table variables are completely isolated to the batch that creates them so no re-resolution has to occur when a CREATE or ALTER statement takes place, which may occur with a temporary table.

and, a little later on the same page:

You can’t use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers [to] a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure.

Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable.

That seems pretty clear. Unfortunately, it’s also pretty clearly incorrect. In this article, I’ll show you two ways to prove it.

Table-Valued Parameters

Let’s get the obvious objection out of the way first.

Table-valued parameters (TVPs) were introduced way back in SQL Server 2008. They allow a local table variable to be passed into a function or procedure.

The documentation says that a table-valued variable is passed to a function or procedure by reference, so the original variable truly is available in the called module’s scope:

Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data.

Finally, the limitations section notes that the reference is read-only within the module:

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

Another restriction imposed by TVPs is that they must be based on a permanent user-defined table type created with CREATE TYPE.

Dynamic SQL

The documentation only mentions using TVPs as read-only input parameters for functions and procedures. The earlier quotation ruled out using table variables from an outer scope with dynamic SQL:

You can’t use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers [to] a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure.

It may be that this entry predates the introduction of TVPs and hasn’t been updated to reflect them. In any case, it certainly is possible to pass a table variable into sp_executesql.

Demo

Let me show you a quick example of passing a table variable into an sp_executesql call.

We will need a user-defined table type:

-- Create a user-defined table type for the TVP
CREATE TYPE dbo.TVP AS table 
(
    i integer NOT NULL PRIMARY KEY
);

Now we can create a table variable of that type, populate it with some rows, and pass it to sp_executesql as a READONLY input parameter. This is just like calling a function or procedure:

-- Create a TVP using the type
DECLARE @TVP AS dbo.TVP;

-- Add some rows
INSERT @TVP (i) 
SELECT [value] 
FROM GENERATE_SERIES(1, 12345);

-- Count the rows using dynamic SQL
EXECUTE sys.sp_executesql
    @stmt =
        N'
        SELECT NumRows = COUNT_BIG(*) 
        FROM @iTVP;
        ',
    -- Note the TVP must be passed input READONLY
    @paramdef = N'@iTVP AS dbo.TVP READONLY',
    -- Set the parameter to the outer table variable
    @iTVP = @TVP;

The demo uses GENERATE_SERIES (new for SQL Server 2022) to populate the table variable, but any alternate method would work just as well.

The code completes successfully, showing 12,345 rows in the table variable.

This demo shows we can pass a table variable into dynamic SQL as a TVP.

Dynamic SQL plans

The execution plans show two different names for the table (@TVP and @iTVP) but these are two different references to the same underlying temporary object.

Execution plans loading the @TVP table and reading from it (as @iTVP) inside the sp_executesql call

@TVP is the original name; @iTVP is used inside sp_executesql. This is largely a coding style choice—the inner name could also be @TVP. I chose different names to emphasise the two references.

You may notice the system-assigned primary key names contain the string #BF3C610 in both references. This is no coincidence—it is the hexadecimal representation of the object ID in tempdb. Both references are to the same underlying temporary object, so the strings match. This confirms that SQL Server passes the table variable by reference, not by creating a copy.

Without a User Defined Type

Using a TVP to pass a table variable to a child scope (a procedure, function, or dynamic SQL) certainly answers the original question to a certain extent:

Is it possible to access a table variable in a scope other than which it is defined?

However, it is not perhaps the most interesting answer, and having to create a permanent user-defined type for the TVP may not always be justified or convenient.

Is there another way?

One by one

Yes, there is, but the table will still be read-only, and you’ll need to accept accessing the table variable’s data one row at a time.

As you probably guessed, I’m talking about using a cursor.

Cursor demo

The following simple demo creates a normal table variable (not a TVP), opens a cursor over it, then reads a row using FETCH inside sp_executesql:

-- Normal table variable, not a TVP
DECLARE @T AS table 
(
    i integer NOT NULL 
    PRIMARY KEY
);

-- Add a row
INSERT @T (i)
VALUES (12345);

-- Cursor definition
DECLARE @c cursor;

SET @c = CURSOR LOCAL
    FORWARD_ONLY
    DYNAMIC
    READ_ONLY
FOR
    SELECT i FROM @T;

-- Open the cursor
-- Closure and deallocation are automatic when 
-- the variable goes out of scope at batch end
OPEN @c;

-- Access the table variable from dynamic SQL
EXECUTE sys.sp_executesql
    @stmt = N'FETCH @icur;',
    @paramdef = N'@icur AS cursor',
    @icur = @c;

This same technique works just as well for stored procedures (including temporary ones), but the cursor parameter must be declared as CURSOR VARYING OUTPUT.

Functions do not allow T-SQL cursor parameters at all, so TVPs are your only option.

Cursor analysis

The execution plans show direct access to the table variable in both scopes:

Plans accessing a table variable in two different scopes

The T-SQL cursor is dynamic so no worktable is involved. The table names are @T in both plans and the hexadecimal object ids #A30CF1E also match.

The cursor is opened as READ_ONLY not because of any particular engine requirement—there is simply no valid syntax to change a table variable created in a higher scope via a T-SQL cursor.

We might make the cursor updateable with SCROLL_LOCKS and try to write something like this in the dynamic SQL:

UPDATE @T
SET i = 0
WHERE CURRENT_OF @icur;

But this will fail because the name @T does not exist in the dynamic SQL’s scope.

We can only access the table variable implicitly via a cursor fetch.

Cursor demo 2

A slightly longer example processing 12,345 rows in the table variable and doing more than a simple FETCH in the dynamic SQL:

SET NOCOUNT, XACT_ABORT ON;

-- Normal table variable, not a TVP
DECLARE @T AS table 
(
    i integer NOT NULL 
    PRIMARY KEY
);

-- Add 12,345 rows
INSERT @T (i)
SELECT [value] 
FROM GENERATE_SERIES(1, 12345);

-- Cursor variable
DECLARE @c cursor;

-- Cursor definition
SET @c = CURSOR LOCAL
    FORWARD_ONLY
    DYNAMIC
    READ_ONLY
FOR
    SELECT i FROM @T;

-- Open the cursor
-- Closure and deallocation are automatic when 
-- the variable goes out of scope at batch end
OPEN @c;

-- Count the rows in the table variable
EXECUTE sys.sp_executesql
    -- Dynamic SQL
    @stmt=
        N'
        -- New table variable to load
        DECLARE @U AS table
        (
            i integer NOT NULL PRIMARY KEY
        );

        -- For cursor FETCH
        DECLARE @i integer;
        
        -- Row copying loop
        WHILE 1 = 1
        BEGIN
            -- Next row
            FETCH @icur 
            INTO @i;

            -- Exit the loop if no more rows
            IF @@FETCH_STATUS = -1 BREAK;

            -- Add the current row to the local table variable
            INSERT @U (i)
            VALUES (@i);

            -- No more plans after the first, please
            -- (first plans available for interest)
            SET STATISTICS XML OFF;
        END;

        -- Return the count
        -- This could also be an OUTPUT variable
        SELECT COUNT_BIG(*) 
        FROM @U;
    ',
    -- Parameter definition
    @paramdef = N'@icur cursor',
    -- Set the cursor parameter
    @icur = @c;    
GO

There’s no particular need to copy the outer table variable into a new one inside the dynamic SQL. We could simply count the rows as we fetch them.

The demo does the extra work just to show that it is possible. We could even name the inner table variable @T without conflict. I did not do that to avoid potential confusion.

The demo copies 12,345 rows into the new table variable and counts them in around 230ms on my modern laptop. That’s not bad for a row-by-row T-SQL cursor operation.

Final Thoughts

Despite what the official product documentation claims, it is possible to access a table variable in a different scope.

The usual way to pass a table variable is by using a TVP, but this method requires a permanent user-defined data type, which isn’t always convenient.

A second method is to define a T-SQL cursor over the table variable and pass that into a procedure or dynamic SQL. This limits processing to one row at a time, but avoids the need for a user-defined data type.

For relatively small table variables, the cursor method may well be adequate. Otherwise, you should choose the TVP.

The cursor does not work with functions because they do not allow cursor type parameters.

You might wonder if the T-SQL cursor technique could be adapted to work with an API cursor to avoid row-by-row access.

The answer is yes and no. Yes, because it is possible. No, because the only way to pass the table variable into the sp_cursoropen call’s embedded SQL is as a READONLY TVP, which defeats the whole point: You might as well just create and use the TVP as normal.

Thanks for reading.