MCDBA Brasil


  • Home
  • Sobre
  • Contato

Livros





Links Rápidos

SQL Server Builds (All Versions/Editions)


Download SQL Server 2017 (trial)


SQL Server 2017 Feature Pack


SQL Server 2016 Feature Pack


Cumulative Update SQL Server 2017 builds


Cumulative Update SQL Server 2016 builds


Cumulative Update SQL Server 2014 builds


Cumulative Update SQL Server 2012 builds


SQL Server 2005/2008 Samples Databases


Documentando o Servidor SQL Server


Analisando a Performance do Servidor-CheckList


Virtual PASS PT


Faça parte do maior virtual chapter do PASS com conteúdos técnicos em Português sobre SQL Server.

Todos os meses um evento Online para você! Acompanhe aqui os WebCasts já agendados

Sindicalize seu blog ou site ao VirtualPASSPT

SQL Server Blogs

SQL Server Query Processing Team


SQL Programmability & API Development Team


SQL Server Manageability Team


Latin America Support Team


Database + Disk + Performance


Microsoft SQL Server Support


SQL CLR Team


SQL Query Optimization Team


SQL 2005 Code Samples


SQL Server Express Team


SQL SMO Samples


SQL Storage Engine Team


SQL CAT Team


SQL Protocols Team


PSS SQL Server Engineers


Slava Oks on SQLOS


Ken Henderson’s blog


LUTI@Microsoft Blog


kimberly L. Trip’s blog


Fernando Garcia Blog

Dica da Semana

Tratando Erros em Procedures Aninhadas

por Nilton Pinheiro julho 25, 2004 Nenhum comentário

Primeiro Nível (PN): Procedure chamada pela aplicação cliente, a procedure de primeiro nível chama a procedure de nível intermediário e a de último nível.


 


Nível Intermediário (NI): Procedure aninhada chamada por outra procedure, a procedure de nível intermediário chama outras procedures intermediárias ou a procedure de último nível.


 


Último Nível (UN): Procedure aninhada chamada por procedures intermediárias ou de primeiro nível; procedures de último nível não chamam qualquer outra procedure.


 


Blocos de tratamento de erro em stored procedures dependem do nível de aninhamento


 


Procedure de Último Nível


CREATE PROCEDURE Proc3


AS


UPDATE Table1 …


SET        @Err = @@ERROR


IF         @Err <> 0


                BEGIN


               RAISERROR (‘Erro na execução da Procedure Proc3’, 16, 1)


               RETURN (@Err)


                END


 


Blocos de tratamento de erros em procedures de último nível devem ser incluídos após cada statement que venha a gerar um erro que possa causar falha na execução da procedure. No exemplo acima, estamos verificando a função de sistema @@Error e se ela for diferente de zero, interrompemos a execução e retornamos o valor de @@Error para a procedure que chamou a Proc3.


 


 


Procedure de Nível Intermediário


CREATE PROCEDURE Proc2


AS


UPDATE Table1


SET         @Err = @@ERROR


IF         @Err <> 0


                BEGIN


               RAISERROR (‘Erro na execução da Procedure Proc2’, 16, 1)


               RETURN (@Err)


           END


 


EXEC @Ret = Proc3


 


SELECT @Ret = coalesce(nullif(@Ret, 0),@@error,1001)


IF @Ret <> 0


        BEGIN


             RAISERROR (‘Erro na execução da Procedure Proc3’, 16, 1)


             RETURN (@Ret)


        END


 


A procedure de nível intermediário tem o mesmo bloco de tratamento de erro após o statement individual e um segundo bloco após a execução da procedure aninhada (Proc3). A função COALESCE retorna a primeira expressão não nula entre seus argumentos.


 


Usando a função COALESCE é possível verificar o parâmetro retornado e a função @@Error ao mesmo tempo. Se @Ret possuir um valor diferente de zero ou nulo, significa que a procedure retornou um valor, garantindo que a mesma não foi executada corretamente.


 


Muitos programadores normalmente utilizam a variável @Ret para obter valores retornados por todas as procedures chamadas em uma procedure, isto é ruim, pois assim não temos garantia de que a procedure chamada executou com sucesso se a primeira for executada com sucesso. Entretanto, no exemplo acima se @Ret for zero, o convertemos  para nulo e adicionalmente verificamos a função @@Error. Se @@Error tiver um valor diferente de zero, então definimos @Ret para o valor de @@Error.


Se quisermos garantir 100% que COALESCE retornará um valor, podemos usar uma constante no terceiro parâmetro (1001 no exemplo).


 


 


Procedure de Primeiro Nível


CREATE PROCEDURE Proc1


AS


BEGIN TRANSACTION


        UPDATE Table1 …


        SET         @Err = @@ERROR


        IF         @Err <> 0


                BEGIN


               ROLLBACK TRANSACTION


               RAISERROR (‘Erro na execução da Procedure Proc1’, 16, 1)


               RETURN (@Err)


                END


 


  EXEC @Ret = Proc2


 


  SELECT @Ret = coalesce(nullif(@Ret, 0),@@error,1001)


  IF @Ret <> 0


        BEGIN


            ROLLBACK TRANSACTION


            RAISERROR (‘Erro na execução da Procedure Proc2’, 16, 1)


            RETURN (@Ret)


        END


COMMIT TRANSACTION


 


Na procedure de primeiro nível temos o mesmo bloco de tratamento de erro após o statement individual e um segundo bloco após a execução da procedure aninhada (Proc2). Adicionalmente a procedure de primeiro nível é responsável por gerenciar a transação. Se algum erro for detectado no bloco de tratamento de erro, será disparado o ROLLBACK TRANSACTION.


 


Embora este modelo permita o compartilhamento de transação entre procedure e evite transações órfãs (BEGIN sem COMMIT), devemos evitar o uso de transações longas. Isto porque em caso de transações longas, os recursos ficarão locados durante todo o tempo de duração da transação, podendo causar blocks entre as conexões que utilizarem os recursos locados. Isto não é bom para a performance do banco de dados. Portanto, ao fazer uso deste modelo de tratamento de erro, evite o uso de transações longas.


 


 


Considerações:


 


Controlando transações na procedure de primeiro nível


Como podemos observar no exemplo acima, o BEGIN, COMMIT e ROLLBACK foram colocados na procedure de primeiro nível. Isto evitará transações órfãs e padronizará o gerenciamento de transações. Como cada ROLLBACK desfaz todas as transações, não necessitamos aninhar ROLLBACKS.


 


Um item importante a se lembrar é disparar um ROLLBACK o mais rápido possível após um erro e liberar todos os recursos locados. Neste modelo, em caso de erro na procedure de último nível (Proc3), retornamos imediatamente o parâmetro de retorno para a intermediária (Proc2) e subimos rapidamente para a procedure de primeiro nível (Proc1) onde disparamos o ROLLBACK.


 


 


Diferença do RETURN em UDF (User Defined Functions)


Em User Defined Functions o commando RETURN possui comportamento totalmente diferente do RETURN em stored procedures. O pior é que a função @@Error tem valor zero mesmo com o erro acontecendo no último statement executado na UDF. Assim, a recomendação é escrever funções claras e curtas, e usar stored procedures em substituição a funções sempre que possível.


 


Para mais informações sobre COALESCE e NULLIF, consulte o Books Online do SQL Server 2000.

Avaliação:
Compartilhe:
  • Anterior Windows 2000 Resource Kit18 anos atrás
  • Próximo Substituindo Cursores no SQL Server 200018 anos atrás

Deixe uma resposta Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

MVP Reconnect Award

Categorias

  • Artigos (359)
  • Dica da Semana (95)
  • Documentação (54)
  • Downloads (113)
  • MSDE 2000 (3)
  • Sem categoria (1)
  • Tutoriais (9)

Posts recentes

  • #FechouBrasil #PartiuPortugal
  • Brigando com o erro “The cached MSI file is missing”
  • MCDBABRASIL está de volta
  • Documentando o Servidor SQL Server
  • Brigando com os Erros 17182, 17826 e 17120

SQL Server AlwaysOn Video Series

Video1: Introdução ao SQLServer2012 AlwaysOn


Video2: Introdução ao SQLServer2012 AlwaysOn Availability Group


Video3: Introdução ao SQLServer2012 AlwaysOn AVG-Demo


Video4: Introdução ao SQLServer2012 AlwaysOn Listener


Video5: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries


Video6: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries-Demo


Video7: Introdução ao SQLServer2012 AlwaysOn Failover Clustering


Serie SQL Server Failover Clustering End-to-End

Parte 1: Configuração da Rede e Ambiente


Parte 2: Configurando o Windows 2008 R2 Domain Controler e DNS


Parte 3: Preparando os nós para o Failover Cluster


Parte 4: Configurando um Failover Cluster de 2 nós


Parte 5: Configurando as LUNs no iSCSI Software Target (Parte 1)


Parte 6: Configurando as LUNs no iSCSI Software Target (Parte 2)


Parte 7: Apresentando as LUNs para os nós do Failover Cluster


Parte 8: Configurando os discos no Failover Cluster


Parte 9: Instalando a primeira instância virtual do SQL Server 2008


Parte 10: Instalando a segunda instância virtual do SQL Server 2008


Parte 11: Instalando e Configurando o MSDTC no Failover Cluster


Parte 12: Configurando Mount Points no Cluster e SQL Server 2008


Vídeo Extra: Removendo uma Instância do SQL Server 2008 R2 em Cluster


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering Overview


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering na Prática

Menu

  • Home
  • Sobre
  • Contato

Mais

  • RSS Feeds
2022 MCDBA Brasil.