none
Execução em LOOP de 'DECLARE' através de valores de um cursor RRS feed

  • Pergunta

  • Code Snippet

        --armazena em um cursor, todos os campos da tabela respectiva, exceto X_INATIVO E EXCLUIDO
        DECLARE CursorCampos CURSOR FOR
                                    SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE TABLE_NAME = @vNomeTabelaNormal AND
                                          COLUMN_NAME <> @vNomeCampoInativo AND
                                          COLUMN_NAME <> 'excluido' AND
                                          COLUMN_NAME <> @vNomeCampoID
                                         
        --Declara duas variáveis auxiliares que irão receber individualmente o nome e tipo de cada campo da linha cursor
        DECLARE @vNomeCampoCursor varchar(200),
                @vTipoCampoCursor varchar(100)
               
        --abre o cursor CursorCampos
        OPEN CursorCampos
             --executa um loop no cursor, linha por linha, até akbar
             WHILE @@FETCH_STATUS = 0
             BEGIN
                  --passa para a próxima linha, armazenando os valores em @vNomeCampoCursor e @vTipoCampoCursor
                  FETCH FROM CursorCampos INTO @vNomeCampoCursor, @vTipoCampoCursor
                  /*conforme o valor de @vNomeCampoCursor e @vTipoCampoCursor, executa declare,
                  definindo uma variável para cada campo da tabela*/
                  SET @vSql = 'DECLARE @v' + UPPER(@vNomeCampoCursor) + ' ' + @vTipoCampoCursor
                      EXEC(@vSql)
                  --recupera os dados do deleted na variável que acabou de ser criada, conforme ainda o nome do campo do primeiro cursor
                  SET @vSql = 'SELECT @v' + UPPER(@vNomeCampoCursor) + ' = ' + @vNomeCampoCursor + ' FROM #Del'
                      EXEC(@vSql)
             END
        --fecha o cursor CursorCampos
        CLOSE CursorCampos

     

     

    Boa noite a todos pessoal!

    A programação acima acontece em uma trigger! Deu pra entender?

    De forma resumida, um cursor deve receber os nomes de campos de uma determinada tabela (@vNomeTabelaNormal), e posteriormente em um loop, uma variável deve ser criada para cada linha do cursor, ou seja, para cada campo.

     

    A compilação ocorre de forma perfeita, porém quando a trigger é executada realmente, o erro 'Must declare the variable @vXXXXX TipoYYY' é retornado.

    O erro está no exec da linha de código DECLARE dinâmica! A variável que ele deve criar de forma dinâmica neste loop não está de forma alguma existente em qualquer linha de código acima!

     

    Gostaria então de pedir um help referente à solução deste problema, de forma que eu ainda possa atingir os mesmos objetivos.

     

    Já em agradecimentos, obrigado pela atenção de todos!

     

    Forte abraço,

    terça-feira, 28 de agosto de 2007 01:08

Respostas

  •  

    Olá Elber!!!

     

     

    Junte o DECLARE e o SELECT num único @sql. O problema está acontecendo pq toda vez que chamamos um EXEC, é aberta uma sessão diferente, ou seja, fazendo o declare num EXEC, quando chamar outro esta variável não mais existirá.

     

     

     

    Abraço

    terça-feira, 28 de agosto de 2007 04:46

Todas as Respostas

  •  

    Olá Elber!!!

     

     

    Junte o DECLARE e o SELECT num único @sql. O problema está acontecendo pq toda vez que chamamos um EXEC, é aberta uma sessão diferente, ou seja, fazendo o declare num EXEC, quando chamar outro esta variável não mais existirá.

     

     

     

    Abraço

    terça-feira, 28 de agosto de 2007 04:46
  •  

    asim nao rola, c. pode explicar melhor o que precisa ?

     

    Abs;

    terça-feira, 28 de agosto de 2007 10:06
  • Sky,

     

    Em ponto do seu script você esta declarando a variável @SQL?

    terça-feira, 28 de agosto de 2007 10:47
  •  

    Orra Alexandre, vlw mesmo kra...
    Fiz o que me aconselhou, e deu mto certo...inclusive em mais partes do código tive que mesclar tudo num exec só, separando os parâmetros por ponto e vírgula...

    Agora está dando um erro em outra coisinha aki, justamente na definição do cursor, acredito...vou estar dando uma destrinchada melhor no código e qualquer dúvida retorno à vocês!

    Só pra desencargo de consciência...na programação, o loop que percorre linha por linha do cursor está correto? não estou obstruindo nenhuma linha, ou cometendo qualquer outra deficiência?...enfim...teria como darem um help com atenção pra mim?

    Forte abraço a todos!

    Brigadão Alexandre! Gracias...

    terça-feira, 28 de agosto de 2007 22:18
  • Marcelo,

     

    na verdade preciso criar um histórico de execuções no banco de dados, determinando ainda versionalização à registros. Se um registro é editado, o registro antigo ainda deve estar armazenado (porém diferenciado por uma flag), e o novo registro deve ser acrescido em mais um numeral de versão, sendo que ainda deve ser possível referenciar ao registro mantido como histórico (pelo qual partiu a alteração), qual sua respectiva versão tbm...

     

    Quando o usuário edita um registro, na trigger, pego o registro do DELETED e reinsiro-o novamente numa outra tabela 'LIXO', simulando uma DatawareHouse e não deixando ainda de vinculá-lo ao registro pelo qual partiu a alteração. Posteriormente passo alguns parâmetros pra uma procedure, que é responsável por trabalhar numa tabela que armazena todas as versões de todos os registros do sistema. Esta procedure acresce a versão do registro atualizado em mais um numeral, e armazena uma versão referente ao registro recuperado para a tabela 'LIXO' também.

     

    Bom, de forma bem resumida é isso...


    O que estou tentando fazer (E ESPERO CONSEGUIR, SE DEUS QUISER!) é que essas triggers que terão de estar em cada tabela, sejam dinâmicas a ponto de eu não necessitar alterar nada de uma para outra (sendo assim eu tbm posso criar uma procedure e centralizar o código talvez). Pra isso, tenho que fazer o banco de dados reconhecer cada campo de cada tabela individualmente, efetuar insert's dinâmicos, buscando valores de forma dinâmica, etc...é um inferno pra quem ainda está bem inexperiente com programação em SQLServer!rs

     

    Deu pra entender?

    Vlw a força...abração!

    terça-feira, 28 de agosto de 2007 22:29
  • JR,

     

    A variável @vSql é declara mais acima, bem no início do procedimento.

    Serve realmente, no código inteiro, apenas para aplicar parâmetros T-SQL de forma dinâmica...

     

    Tranquilo?
    Vlw a força, abraços!

     

    terça-feira, 28 de agosto de 2007 22:31
  • GALERA! Graças a Deus consegui consertar vários erros aqui e o código está funcionando perfeitamente!

    Tive que efetuar várias alterações nas programações referentes aos loops de cursores, etc...

    Enfim...

    Gostaria de agradecer a todos que deram atenção, e aproveito para passar o código completo para quem se interessar!

     

    Abraço a todos!

     

    CÓDIGO COMPLETO DA TRIGGER DE MANUTENÇÃO DE HISTÓRICO E VERSIONALIZAÇÃO

    BEGIN

    SET NOCOUNT ON

    /*as variáveis abaixo declaradas servem apenas para facilitação na reutilização deste código trigger,
    evitando demasiadas alterações no corpo efetivo da trigger, mas sim passando todos os dados pela setagem
    destas respectivas variáveis*/
    DECLARE @vNomeTabelaNormal varchar(200),
            @vNomeCampoID varchar(200),
            @vNomeTabelaGarbage varchar(200),
            @vNomeCampoInativo varchar(200)

    SET @vNomeTabelaNormal = 'rms_c_alternatividade'
    SET @vNomeCampoID = SUBSTRING(@vNomeTabelaNormal, 7, LEN(@vNomeTabelaNormal))         --entrada automática
    SET @vNomeCampoInativo = @vNomeCampoID + '_inativo'                                   --entrada automática
    SET @vNomeCampoID = @vNomeCampoID + '_id'                                             --entrada automática
    SET @vNomeTabelaGarbage = @vNomeTabelaNormal + '_GB'                                  --entrada automática

    -->>>>>>> CORPO EFETIVO DA TRIGGER >>> CORPO EFETIVO DA TRIGGER >>> CORPO EFETIVO DA TRIGGER >>> CORPO EFETIVO DA TRIGGER>>>

    DECLARE @vTipo int, --variável booleana que definirá o tipo de ativação da procedure
       /*declara uma variável que irá receber o id do último cadastro e
       uma variável que recebera a id da empresa do último cadastro.*/
            @vIDItem int,
            @vEmpresa int,
           
            @vSql nvarchar(4000), --variável string que receberá parâmetros sql ao longo da trigger
            @vSql2 nvarchar(4000), --variável string que receberá parâmetros sql ao longo da trigger
            @vControle sql_variant --variável de controle de qualquer tipo, apenas para trabalho em lógica
           
    /* Verifica se a trigger está sendo disparada por uma ação de INSERT OU
    UPDATE. Caso INSERT, fará apenas o cadastro da versão 0. Caso UPDATE
    fará a versionalização crescente. Chamando procedures diferentes.*/
    IF EXISTS(SELECT @vNomeCampoID FROM DELETED)
    BEGIN --begin do IF
    -->>>>>>>EXECUÇÃO ATUALIZAÇÃO:
        --define o tipo de execução da procedure
        SET @vTipo = 1
       
        --passa os dados de deleted para uma tabela temporária chamada #DEL
        SELECT * INTO #Del FROM DELETED
               /* Esta passagem de dados de DELETED para uma tabela temporária doravante #X
                  é necessária, já que logo abaixo teremos um sp_executesql que não conseguirá
                  buscar dados da tabela DELETED, considerando que no âmbito de sua execução
                  esta sairia do espaço da TRIGGER, já que considerações DELETED/INSERTED só existem
                  dentro de TRIGGERS. */
       
        --armazena em um cursor, todos os campos da tabela respectiva, exceto X_INATIVO E EXCLUIDO
        DECLARE CursorCampos CURSOR FOR
                                    SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
                                    WHERE TABLE_NAME = @vNomeTabelaNormal AND
                                          COLUMN_NAME <> @vNomeCampoInativo AND
                                          COLUMN_NAME <> 'empresa' AND
                                          COLUMN_NAME <> @vNomeCampoID
                                         
        --Declara duas variáveis auxiliares que irão receber individualmente o nome e tipo de cada campo da linha cursor
        DECLARE @vNomeCampoCursor varchar(200),
                @vTipoCampoCursor varchar(100)
               
        --abre o cursor CursorCampos
        OPEN CursorCampos
        FETCH NEXT FROM CursorCampos INTO @vNomeCampoCursor, @vTipoCampoCursor
             -- zera o valor de @vSql para assegurar uma concatenação de string perfeita
             SET @vSql = ''

             --executa um loop no cursor, linha por linha, até akbar
             WHILE @@FETCH_STATUS = 0
             BEGIN
                  /*conforme o valor de @vNomeCampoCursor e @vTipoCampoCursor, executa declare,
                  definindo uma variável para cada campo da tabela*/
                  SET @vSql = @vSql + 'DECLARE @v' + UPPER(@vNomeCampoCursor) + ' ' + @vTipoCampoCursor + '; '
                  --recupera os dados do deleted na variável que acabou de ser criada, conforme ainda o nome do campo do primeiro cursor
                  SET @vSql = @vSql + 'SET @v' + UPPER(@vNomeCampoCursor) + ' = (SELECT ' + @vNomeCampoCursor + ' FROM #Del); '
                  --passa para a próxima linha, armazenando os valores em @vNomeCampoCursor e @vTipoCampoCursor
                  FETCH NEXT FROM CursorCampos INTO @vNomeCampoCursor, @vTipoCampoCursor
             END
        --fecha o cursor CursorCampos
        CLOSE CursorCampos
       
        --armazena o valor do item atualizado em @vIDItem, recuperando os dados de DELETED
        SET @vSql2 = N'SELECT @vIDItem = ' + @vNomeCampoID + ' FROM #Del'
            EXEC sp_executesql @vSql2,N'@vIDItem varchar(400) OUTPUT',@vIDItem = @vIDItem OUTPUT
        --armazena o valor de EMPRESA
        SET @vEmpresa = (SELECT empresa FROM #Del)

        --reinserir registro com dados coletados do deleted em GARBAGE (GB), sempre com status inativo (duplicação)
        SET @vSql = @vSql + 'INSERT INTO ' + @vNomeTabelaGarbage + ' (' + @vNomeCampoID + 'item,empresa,' + @vNomeCampoInativo

        OPEN CursorCampos
        FETCH NEXT FROM CursorCampos INTO @vNomeCampoCursor, @vTipoCampoCursor
             --determinação da sql por loop:
             WHILE @@FETCH_STATUS = 0
             BEGIN
                  SET @vSql = @vSql + ',' + @vNomeCampoCursor
                  FETCH NEXT FROM CursorCampos INTO @vNomeCampoCursor, @vTipoCampoCursor
             END
        CLOSE CursorCampos
       
        SET @vSql = @vSql + ') VALUES (' + CAST(@vIDItem AS VARCHAR) + ',' + CAST(@vEmpresa AS VARCHAR) + ', 1'
       
        OPEN CursorCampos
        FETCH NEXT FROM CursorCampos INTO @vNomeCampoCursor, @vTipoCampoCursor
             WHILE @@FETCH_STATUS = 0
             BEGIN
                   SET @vSql = @vSql + ',' + 'CAST(@v' + UPPER(@vNomeCampoCursor) + ' AS VARCHAR)'
                   FETCH NEXT FROM CursorCampos INTO @vNomeCampoCursor, @vTipoCampoCursor
             END
        CLOSE CursorCampos
        DEALLOCATE CursorCampos

        SET @vSql = @vSql + ');'
        --executa a @vSql de reinclusão garbage definida acima
        EXEC(@vSql)

    END --end do IF
    ELSE
    BEGIN --begin do else do if
    -->>>>>>>EXECUÇÃO CADASTRO:
       --define o tipo de execução da procedure
       SET @vTipo = 0

        --passa os dados de deleted para uma tabela temporária chamada #DEL
        SELECT * INTO #Ins FROM INSERTED

               --DEFINE OS PARÂMETROS A SEREM ENVIADOS PARA A PROCEDURE:
               SET @vSql2 = N'SELECT @vIDItem = ' + @vNomeCampoID + ' FROM #Ins'
               EXEC sp_executesql @vSql2,N'@vIDItem varchar(400) OUTPUT',@vIDItem = @vIDItem OUTPUT
               SET @vEmpresa = (SELECT empresa FROM INSERTED)

    END --end do else do if

     --PASSA O NOME DA TABELA E OS PARÂMETROS TRABALHADOS NESTA TRIGGER PARA A PROCEDURE DE INSERÇÃO
     EXEC procedure_Versao @vNomeTabelaNormal, @vIDItem, @vEmpresa, @vTipo


    SET NOCOUNT OFF

    END

     

     

     

     

    terça-feira, 28 de agosto de 2007 23:57
  •  

    Maravilha Elber!!

     

    Qualquer problema estamos aqui para tentar ajudar.

     

     

    Abraço

    quarta-feira, 29 de agosto de 2007 01:01