Qual é o melhor método para adicionar tratamento de erros em procedimentos armazenados do SQL 2005?

Alex Kuznetsov tem um ótimo capítulo em seu livro Defensive Database Programming (Capítulo 8) que aborda T-SQL TRY...CATCH, transações T-SQL e configurações SET XACT_ABORT e o uso de tratamento de erros do lado do cliente. Isso o ajudará muito a decidir qual das opções faz mais sentido para o que você precisa realizar.

Está disponível gratuitamente neste site . Não sou de forma alguma afiliado à empresa, mas possuo a versão impressa desse livro.

Existem muitos pequenos detalhes sobre esse assunto que são explicados muito bem pelo Alex.

A pedido do Nick... (mas nem tudo está no capítulo)

Em termos de dimensionamento, você precisa ser totalmente honesto sobre quais atividades precisam estar no código db e quais devem estar no aplicativo. Você já notou como o código de execução rápida tende a voltar a ser projetado para uma única preocupação por método?

A maneira mais fácil de se comunicar seria códigos de erro personalizados (> 50.000). Também é bem rápido. Isso significa que você teria que manter o código db e o código do aplicativo sincronizados. Com um código de erro personalizado, você também pode retornar informações úteis na string da mensagem de erro. Como você tem um código de erro estritamente para essa situação, pode escrever um analisador no código do aplicativo adaptado ao formato de dados do erro.

Além disso, quais condições de erro precisam de lógica de repetição no banco de dados? Se você quiser tentar novamente após X segundos, é melhor lidar com isso no código do aplicativo para que a transação não bloqueie tanto. Se você estiver apenas reenviando uma operação DML imediatamente, repeti-la no SP pode ser mais eficiente. Lembre-se, porém, de que possivelmente será necessário duplicar o código ou adicionar uma camada de SPs para realizar uma nova tentativa.

Realmente, esse é atualmente o maior problema com a lógica TRY...CATCH no SQL Server no momento. Isso pode ser feito, mas é um pouco idiota. Procure algumas melhorias chegando a isso no SQL Server 2012, especialmente lançando novamente exceções do sistema (preservando o número de erro original). Além disso, há FORMATMESSAGE , que adiciona alguma flexibilidade na construção de mensagens de erro, especialmente para fins de log.

Este é o nosso modelo (registro de erros removido)

Notas:

• Sem XACT_ABORT, todos os TXN começam e commit/rollbacks devem ser pareados
• Um commit diminui @@TRANCOUNT
• Uma reversão retorna @@TRANCOUNT para zero, então você obteria o erro 266
• Você não pode fazer ROLLBACK apenas na camada atual (por exemplo, decrementar @@TRANCOUNT na reversão)
• XACT_ABORT suprime o erro 266
• Cada proc armazenado deve estar em conformidade com o mesmo modelo para que cada chamada seja atômica
• A verificação de reversão é realmente redundante por causa de XACT_ABORT. No entanto, isso me faz sentir melhor, parece estranho sem e permite situações em que você não o quer.
• Isso permite TXNs do lado do cliente (como LINQ)
• Remus Rusanu tem um shell semelhante que usa pontos de salvamento. Eu prefiro uma chamada de banco de dados atômica e não uso atualizações parciais como o artigo deles

...portanto, não crie mais TXNs do que o necessário

No entanto,

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Eu uso Try/Catch, mas também reúno o máximo de informações possível e as escrevo em um errorlog DEPOIS da reversão. Neste exemplo, "LogEvent" é um procedimento armazenado que grava em uma tabela EventLog, contendo os detalhes do que aconteceu. GetErrorInfo() é uma chamada de função que retorna a mensagem de erro exata.

Quando ocorre um erro, as informações são coletadas, o procedimento pula para a seção de tratamento de erros e emite uma reversão. As informações são gravadas no log e, em seguida, o procedimento é encerrado.

Considerando as chamadas extras de procedimento/função envolvidas, parece um pouco exagerado. NO ENTANTO, esse método é extremamente útil ao tentar depurar o problema.

exec LogEvent @Process, @Database, 'Tentando inserir blá blá blá'
COMECE A TENTAR
  inserir em MyTable
  selecione valores
    de MinhaOutraTabela

  selecione @rowcount = @@ROWCOUNT
FIM DA TENTATIVA
-- Manipulação de erros
COMEÇAR A PESCAR
  selecione @erro = ERROR_NUMBER(),
         @contagemde linhas = -1,
         @TableAction = 'inserir',
         @TableName = @Database + '.MinhaTabela',
         @AdditionalInfo = '(Tentativa de inserir blá blá blá) ' + dbo.GetErrorInfo()
   GOTO TableAccessError
FINALIZAR

.
.
.
.

TableAccessError:
SE (@@TRANCOUNT > 0) ROLLBACK
selecione @saída = superior(@TableAction) +
       ' ERRO - Ocorreu um erro enquanto ' +
       caso (@TableAction)
         quando 'atualizar' então 'atualizar'
         quando 'excluir', então 'excluir'
         else @TableAction + 'ing'
       fim +
       'registros' +
       case(@TableAction)
         quando 'selecionar' então 'de'
         quando 'atualizar' então 'in'
         quando 'inserir' então 'em'
         senão 'de'   
         fim +
         ' a ' + @TableName + ' tabela.'
select @output = @output + ' @@ERROR: ' + convert(varchar(8),@error)
select @output = @output + ' @@ROWCOUNT: ' + convert(varchar(8),@rowcount)

select @output = @output + isnull(@AdditionalInfo,'')
exec LogEvent @Process, @Database, @Output
RAISERROR(@output,16,1) com log
selecione @ReturnCode = -1
IR PARA THE_EXIT