sql-server - 必须声明标量变量

Question

我在存储过程中写了这个 SQL 但没有工作，

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')

此 SQL 引发此错误:

Must declare the scalar variable "@value1".

我正在动态生成 SQL，我想在变量中获取值。我该怎么办？

Answer 1

您收到 DECLARE 的原因动态语句中的错误是因为动态语句是分批处理的，归结为范围问题。虽然 SQL Server 中可用的范围可能有更正式的定义，但我发现通常记住以下三个就足够了，从最高可用性到最低可用性排序:

全局 :

在服务器范围内可用的对象，例如使用双井号/井号( ##GLOBALTABLE ，但您喜欢调用 # )创建的临时表。对全局对象要非常小心，就像对任何应用程序、SQL Server 或其他应用程序一样；通常最好完全避免这些类型的事情。我的主要意思是要牢记这个范围，特别是作为提醒远离它。

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.

专场 :

引用锁定到特定 spid 的对象。在我的脑海里，我能想到的唯一类型的 session 对象是一个普通的临时表，定义为#Table。在 session 范围内本质上意味着在批处理(由 GO 终止)完成后，对该对象的引用将继续成功解析。 These are technically accessible by other sessions ，但是以编程方式这样做会有些壮举，因为它们在 tempdb 中获得了某种随机名称，并且无论如何访问它们都有些麻烦。

-- 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;

打开一个新 session (具有单独 spid 的连接)，上面的第二批将失败，因为该 session 将无法解析 #t_Test对象名称。

批次 :

正常变量，例如您的 @value1和 @value2 , 仅适用于声明它们的批次。不像 #Temp表，只要您的查询块命中 GO ，这些变量不再可用于 session 。这是产生错误的范围级别。

-- 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;

好吧，那又怎样？

您的动态声明中发生的情况是 EXECUTE()命令有效地评估为一个单独的批处理，而不会破坏您从中执行它的批处理。 EXECUTE()很好，但自从 sp_executesql() 推出以来，我只在最简单的情况下使用前者(明确地，当我的语句中几乎没有“动态”元素时，主要是为了“欺骗”否则不适应的 DDL CREATE 语句在其他批次的中间运行)。 @AaronBertrand's上面的答案与下面的答案相似，并且在性能上与下面的相似，在评估动态语句时利用优化器的功能，但我认为在 @param 上进行扩展可能是值得的。 ，嗯，参数。

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;