Início / dba / Perguntas / 23994
Accepted
Sivajith
Asked: 2012-09-10 22:14:26 +0800 CST

EXECUTE sp_executesql

  • 772

Eu tenho um problema com EXECUTE sp_executesql e estou preso a ele. minha dúvida é a seguinte

   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

mas o problema é quando executar esta mensagem de erro vem como este nome de objeto inválido '#T0'. Nome de objeto inválido '#T1'. Nome de objeto inválido '#T2'.

sql-server

2 respostas

  1. Best Answer

    Supondo que as tabelas temporárias que você está referenciando existam, seu problema é o escopo da sessão. As tabelas temporárias marcadas com um único #são visíveis para a sessão em que são criadas, elas também são conhecidas como tabelas temporárias locais.

    O problema que você tem é que, quando você executa um sql dinâmico, ele é executado em uma nova sessão - portanto, essa sessão não pode ver as tabelas temporárias que você criou.

    Para corrigir o problema, você pode fazer uma das três coisas:

    • Crie a tabela temporária dentro do seu sql dinâmico
    • Use uma tabela temporária global usando ##como estes são visíveis em todas as sessões
    • Redesenhe seu código para inserir em uma variável de tabela e retorne isso como um parâmetro de saída parasp_executesql

    Espero que isso ajude você.

    • 3

  2. O SQL dinâmico é executado na mesma sessão que o código de chamada. A questão não é a sessão, mas o escopo. Seu SQL dinâmico cria as tabelas temporárias dentro da sp_executesqlchamada e assim a tabela #temp criada só fica visível dentro dessa sp_executesql chamada, conforme documentado no MSDN:

    Tabelas temporárias são descartadas automaticamente quando saem do escopo, a menos que explicitamente descartadas usando DROP TABLE:

    • Uma tabela temporária local criada em um procedimento armazenado é eliminada automaticamente quando o procedimento armazenado é concluído . A tabela pode ser referenciada por qualquer procedimento armazenado aninhado executado pelo procedimento armazenado que criou a tabela. A tabela não pode ser referenciada pelo processo que chamou o procedimento armazenado que criou a tabela.

    • Todas as outras tabelas temporárias locais são descartadas automaticamente no final da sessão atual.

    • As tabelas temporárias globais são descartadas automaticamente quando a sessão que criou a tabela termina e todas as outras tarefas param de fazer referência a elas. A associação entre uma tarefa e uma tabela é mantida apenas durante a vida útil de uma única instrução Transact-SQL. Isso significa que uma tabela temporária global é descartada na conclusão da última instrução Transact-SQL que estava referenciando ativamente a tabela quando a sessão de criação terminou.

    Então você precisa reter a tabela #temp em um escopo que abranja tanto a criação quanto o uso, por exemplo (também corrigiu os problemas de injeção de SQL que você tinha e passou @WAGECODEcomo parâmetro):

    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;

    Escusado será dizer que este é um uso desnecessário de tabelas #temp, você pode simplesmente selecionar o resultado que deseja e ligar para ti um dia, não há necessidade de tabelas #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

relate perguntas