主页 / dba / 问题 / 23994
Accepted
Sivajith
Asked: 2012-09-10 22:14:26 +0800 CST

执行 sp_executesql

  • 772

我遇到了 EXECUTE sp_executesql 的问题并坚持使用它。我的查询如下

   DECLARE @TABLE_NAME VARCHAR(5)='#T'
   DECLARE @TABLE_ID INTEGER=0
   DECLARE @QRY NVARCHAR(MAX) =''
   DECLARE @TABLE VARCHAR(3)
 DECLARE CRSR_WAGES CURSOR FOR SELECT Code,Name , ROW_NUMBER() OVER (ORDER BY U_Prrty, DocEntry) Slno, COUNT(Code) OVER (PARTITION BY OBJECT) ColNos
                                     FROM  [@CCS_WAGE] 
                                     WHERE  U_DISPP = 'Y' AND U_DEDWT='E'                        
OPEN CRSR_WAGES
FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS
WHILE @@FETCH_STATUS=0
BEGIN
    SET @TABLE=@TABLE_NAME+CONVERT(VARCHAR(2),@TABLE_ID)
    SELECT @TABLE AS 'TABLE'
    SET @QRY='(SELECT empno, amt AS '+'['+@WAGE_DESC+']'
    SET @QRY=@QRY+' INTO '+@TABLE
    SET @QRY=@QRY+' FROM CCS_PYMONTHRESULT WHERE wgcod='''+@WAGECOD+''')'
    SELECT @QRY
    EXECUTE sp_executesql @QRY
    SET @TABLE_ID=@TABLE_ID+1
    DECLARE @STAR AS NVARCHAR(50)='SELECT * FROM ' + @TABLE
    SELECT @STAR
    EXECUTE sp_executesql @STAR
    FETCH NEXT FROM CRSR_WAGES INTO @WAGECOD,@WAGE_DESC,@SI_NO,@COL_NOS                    
        END
CLOSE CRSR_WAGES
DEALLOCATE CRSR_WAGES

但问题是当执行此错误消息时，就像这个无效的对象名称'#T0'。无效的对象名称“#T1”。无效的对象名称“#T2”。

sql-server

2 个回答

  1. Best Answer

    假设您引用的临时表存在，那么您的问题是会话范围。标记为 single 的临时表#对创建它们的会话可见，它们也称为本地临时表。

    您遇到的问题是，当您执行一段动态 sql 时，它会在新会话中运行 - 因此该会话无法看到您创建的临时表。

    要解决此问题，您可以执行以下三件事之一：

    • 在动态 sql 中创建临时表
    • 使用全局临时表，##因为它们在所有会话中都是可见的
    • 重新设计代码以插入表变量并将其作为输出参数返回给sp_executesql

    我希望这可以帮助你。

    • 3

  2. 动态 SQL 与调用代码在同一会话中运行。问题不是会话，而是范围。您的动态 SQL 在sp_executesql调用中创建临时表，因此创建的 #temp 表仅在该 sp_executesql调用中可见，如 MSDN 中所述：

    临时表超出范围时会自动删除，除非使用 DROP TABLE 显式删除：

    • 当存储过程完成时，会自动删除在存储过程中创建的本地临时表。该表可以被创建该表的存储过程执行的任何嵌套存储过程引用。调用创建该表的存储过程的进程不能引用该表。

    • 所有其他本地临时表在当前会话结束时自动删除。

    • 当创建表的会话结束并且所有其他任务都停止引用它们时，将自动删除全局临时表。任务和表之间的关联仅在单个 Transact-SQL 语句的生命周期内维护。这意味着在创建会话结束时主动引用表的最后一个 Transact-SQL 语句完成时，将删除全局临时表。

    因此，您需要在涵盖创建和使用的范围内保留 #temp 表，例如（还修复了您遇到的 SQL 注入问题并@WAGECODE作为参数传递）：

    SET @TABLE=@TABLE_NAME+CONVERT(VARCHAR(2),@TABLE_ID);
SET @QRY ='(SELECT empno, amt AS '+ QUOTENAME(@WAGE_DESC);
SET @QRY +=' INTO '+@TABLE;
SET @QRY +=' FROM CCS_PYMONTHRESULT WHERE wgcod=@WAGECOD)';
SET @QRY += '
          SELECT * FROM ' + @TABLE;
EXECUTE sp_executesql @QRY, N'@WAGECODE <typeof@wagecode>', @WAGECODE;

    不用说，这是对#temp表的不必要使用，您可以简单地选择您想要的结果并每天调用ti，不需要#temp表：

    SET @QRY ='(SELECT empno, amt AS '+ QUOTENAME(@WAGE_DESC);
SET @QRY +=' FROM CCS_PYMONTHRESULT WHERE wgcod=@WAGECOD)';
EXECUTE sp_executesql @QRY, N'@WAGECODE <typeof@wagecode>', @WAGECODE;
    • 2

相关问题