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.