Must declare the scalar variable

Question

I wrote this SQL in a stored procedure but not working,

declare @tableName varchar(max) = 'TblTest'
declare @col1Name varchar(max) = 'VALUE1'
declare @col2Name varchar(max) = 'VALUE2'
declare @value1 varchar(max)
declare @value2 varchar(200)

execute('Select TOP 1 @value1='+@col1Name+', @value2='+@col2Name+' From '+ @tableName +' Where ID = 61')

select @value1

execute('Select TOP 1 @value1=VALUE1, @value2=VALUE2 From TblTest Where ID = 61')

This SQL throws this error:

Must declare the scalar variable "@value1".

I am generating the SQL dynamically and I want to get value in a variable. What should I do?

Answer 1

The reason you are getting the DECLARE error from your dynamic statement is because dynamic statements are handled in separate batches, which boils down to a matter of scope. While there may be a more formal definition of the scopes available in SQL Server, I've found it sufficient to generally keep the following three in mind, ordered from highest availability to lowest availability:

Global:

Objects that are available server-wide, such as temporary tables created with a double hash/pound sign ( ##GLOBALTABLE, however you like to call # ). Be very wary of global objects, just as you would with any application, SQL Server or otherwise; these types of things are generally best avoided altogether. What I'm essentially saying is to keep this scope in mind specifically as a reminder to stay out of it.

IF ( OBJECT_ID( 'tempdb.dbo.##GlobalTable' ) IS NULL )
BEGIN
    CREATE TABLE ##GlobalTable
    (
        Val             BIT
    );

    INSERT INTO ##GlobalTable ( Val )
    VALUES ( 1 );
END;
GO

-- This table may now be accessed by any connection in any database,
-- assuming the caller has sufficient privileges to do so, of course.

Session:

Objects which are reference locked to a specific spid. Off the top of my head, the only type of session object I can think of is a normal temporary table, defined like #Table. Being in session scope essentially means that after the batch ( terminated by GO ) completes, references to this object will continue to resolve successfully. These are technically accessible by other sessions, but it would be somewhat of a feat do to so programmatically as they get sort of randomized names in tempdb and accessing them is a bit of a pain in the ass anyway.

-- Start of session;
-- Start of batch;
IF ( OBJECT_ID( 'tempdb.dbo.#t_Test' ) IS NULL )
BEGIN
    CREATE TABLE #t_Test
    (
        Val     BIT
    );

    INSERT INTO #t_Test ( Val )
    VALUES ( 1 );
END;
GO 
-- End of batch;

-- Start of batch;
SELECT  *
FROM    #t_Test;
GO
-- End of batch;

Opening a new session ( a connection with a separate spid ), the second batch above would fail, as that session would be unable to resolve the #t_Test object name.

Batch:

Normal variables, such as your @value1 and @value2, are scoped only for the batch in which they are declared. Unlike #Temp tables, as soon as your query block hits a GO, those variables stop being available to the session. This is the scope level which is generating your error.

-- Start of session;
-- Start of batch;
DECLARE @test   BIT = 1;

PRINT @test;
GO
-- End of batch;

-- Start of batch;
PRINT @Test;  -- Msg 137, Level 15, State 2, Line 2
              -- Must declare the scalar variable "@Test".
GO
-- End of batch;

Okay, so what?

What is happening here with your dynamic statement is that the EXECUTE() command effectively evaluates as a separate batch, without breaking the batch you executed it from. EXECUTE() is good and all, but since the introduction of sp_executesql(), I use the former only in the most simple of instances ( explicitly, when there is very little "dynamic" element of my statements at all, primarily to "trick" otherwise unaccommodating DDL CREATE statements to run in the middle of other batches ). @AaronBertrand's answer above is similar and will be similar in performance to the following, leveraging the function of the optimizer when evaluating dynamic statements, but I thought it might be worthwhile to expand on the @param, well, parameter.

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'TblTest'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.TblTest;
    CREATE TABLE dbo.TblTest
    (
        ID      INTEGER,
        VALUE1  VARCHAR( 1 ),
        VALUE2  VARCHAR( 1 )
    );

    INSERT INTO dbo.TblTest ( ID, VALUE1, VALUE2 )
    VALUES ( 61, 'A', 'B' );
END;

SET NOCOUNT ON;

DECLARE @SQL    NVARCHAR( MAX ),
        @PRM    NVARCHAR( MAX ),
        @value1 VARCHAR( MAX ),
        @value2 VARCHAR( 200 ),
        @Table  VARCHAR( 32 ),
        @ID     INTEGER;

    SET @Table = 'TblTest';
    SET @ID = 61;

    SET @PRM = '
        @_ID        INTEGER,
        @_value1    VARCHAR( MAX ) OUT,
        @_value2    VARCHAR( 200 ) OUT';
    SET @SQL = '
        SELECT  @_value1 = VALUE1,
                @_value2 = VALUE2
        FROM    dbo.[' + REPLACE( @Table, '''', '' ) + ']
        WHERE   ID = @_ID;';

EXECUTE dbo.sp_executesql @statement = @SQL, @param = @PRM,
            @_ID = @ID, @_value1 = @value1 OUT, @_value2 = @value2 OUT;

PRINT @value1 + ' ' + @value2;

SET NOCOUNT OFF;