Tratamento de Exceções em Script SQL

[Guest Jefferson Alves]

Boa tarde pessoal,

Por favor, estou precisando de ajuda para adequar este arquivo de script em SQL, de forma a tratar as exceções e informar o usuário sobre o andamento das operações executadas por ele.

O primeiro problema é que o parse está reclamando do escopo das variáveis quando declaradas fora de uma seção BEGIN TRY / BEGIN CATCH.

O segundo problema é a declaração da criação de uma stored procedure / trigger dentro desta seção BEGIN TRY / BEGIN CATCH. O parse me obriga a transferir este código para fora da seção, por que ?

Imaginem que o problema esteja dividido em múltiplos arquivos, cada qual responsável por uma ou mais tarefas semelhantes e sendo que estes arquivos devem ser executados na sequência devido a questões de dependência entre objetos. Por isso enviei apenas este arquivo de exemplo, mas os problemas aparecem para outros arquivos semelhantes.

Segue abaixo o código do arquivo:

/**************************************************/
--       << DEFINE O BANCO DE DADOS >>     --

USE NOME_BANCO;
PRINT 'Script: BL001_BD_0500_STORED_PROCEDURES'
PRINT ''
/**************************************************/
--
/**************************************************/
--      << MONITORA A EXECUÇÃO DO SCRIPT >>     --

--OBTÉM O IDENTIFICADOR E IMCREMENTA A EXECUÇÃO
--
DECLARE 
    @NUM_IDENT    INT,
    @NOME_PROJETO    VARCHAR(50)

SET @NOME_PROJETO = ''

SELECT @NUM_IDENT = ISNULL(MAX(NUM_IDENT),0) FROM DICMONITOR
    WHERE    PROJETO = @NOME_PROJETO AND SCRIPT = 'BL001_BD_0500_STORED_PROCEDURES' AND 
        CONVERT(CHAR, DATA_EXEC, 103) = CONVERT(CHAR, GETDATE(), 103) 

SET @NUM_IDENT = @NUM_IDENT + 1


--REGISTRA A EXECUÇÃO DO SCRIPT
--
IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE TYPE = 'U' AND NAME = 'DICMONITOR')
    INSERT INTO DICMONITOR(PROJETO,DATA_EXEC,NUM_IDENT,SCRIPT,STATUS,MENSAGEM,HORA_INI,HORA_FIM,LASTUPDATE,USERNAME)
        VALUES(@NOME_PROJETO,GETDATE(), @NUM_IDENT,'BL001_BD_0500_STORED_PROCEDURES','INICIO','',GETDATE(),NULL,GETDATE(),CURRENT_USER)

/**************************************************/
--
/*
-- =====================================================================
-- Author.....: 
-- Create date: 99/99/99
--
-- Object.....: [PROC001] Procedurename
-- Description:    O bloco BEGIN TRY abaixo é responsável pela criação do 
--        objeto STORED PROCEDURE.  Em caso de erro por qualquer 
--        motivo, toda a transação será cancelada e o erro reportado 
--        e registrado.
--
-- Comments...: 
-- =====================================================================
*/
DECLARE 
    @OBJ_TIPO    VARCHAR(10),    -- [C]  CHECK constraint,    [F]  FOREIGN KEY constraint,
                    -- [PK] PRIMARY KEY constraint, [P]  SQL stored procedure,
                    -- [U]  Table (user-defined),    [UQ] UNIQUE constraint,
                    -- [V]  View,            [TR] SQL_TRIGGER
    @OBJ_PROC    VARCHAR(40),
    @OBJ_NOME_1    VARCHAR(40),
    @OBJ_NOME_2    VARCHAR(40),
    @OBJ_CAMPO    VARCHAR(40),
    @MSG_ERRO    VARCHAR(400),
    @TRATA_ERRO    INT,        -- [0] NÃO TRATA ERRO        , [1] TRATA O ERRO
    @CONTINUA_EXEC    INT        -- [0] NÃO CONTINUA A EXEC. , [1] CONTINUA A EXECUÇÃO


/*
-- =====================================================================
-- Auditoria: 
--
--    1) Verifica a existência das tabelas/views necessárias.
-- =====================================================================
*/
-- INICIALIZA OS NOMES DOS OBJETOS DEPENDENTES
SET @OBJ_PROC   = ''
SET @OBJ_NOME_1 = ''
SET @OBJ_NOME_2 = ''
SET @OBJ_CAMPO  = ''
SET @OBJ_TIPO   = 'U'

IF NOT EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE TYPE = @OBJ_TIPO AND NAME IN (@OBJ_NOME_1, @OBJ_NOME_2))
BEGIN
    SET @MSG_ERRO = 'MESSAGE=Algum objeto referenciado nas procedures criadas não foi localizado e o script será interrompido !'
    PRINT @MSG_ERRO 
    RETURN
END
ELSE
BEGIN
    SET @MSG_ERRO = 'MESSAGE=Todos os objetos referenciados nas procedures estão disponíveis para uso !'
    PRINT @MSG_ERRO 
END


BEGIN TRY
    -- INICIA A TRANSAÇÃO E DEFINE O TRATAMENTO DE ERROS E A CONTINUIDADE NO SCRIPT...
    SET XACT_ABORT OFF;
    BEGIN TRAN     
    SET @TRATA_ERRO    = 1
    SET @CONTINUA_EXEC = 1

    -- INÍCIO DE EXECUÇÃO DAS OPERAÇÕES...
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    
    -- =============================================
    -- Author.....:
    -- Create date: 99/99/99
    -- Description:    
    -- =============================================
    CREATE PROCEDURE [dbo].[SP_NOME_PROCEDURE] 
    (
        @PARAMETRO INT
    )
    AS
    BEGIN

        DECLARE
            @VARIAVEL    INT

        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Executa as operações
    END
    
    SET ANSI_NULLS OFF
    SET QUOTED_IDENTIFIER OFF
    SET ANSI_PADDING OFF    

    -- FINALIZA A TRANSAÇÃO ATUAL...
    COMMIT TRAN
    PRINT 'MESSAGE=A transação atual foi concluída com sucesso !'
END TRY
BEGIN CATCH
    --REGISTRA A EXECUÇÃO DO SCRIPT
    --
    IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE TYPE = 'U' AND NAME = 'DICMONITOR')
        UPDATE DICMONITOR 
        SET 
            HORA_FIM = GETDATE(), 
            STATUS = 'ERRO', 
            MENSAGEM = ERROR_MESSAGE(),
            LASTUPDATE = GETDATE()
        WHERE
            PROJETO = @NOME_PROJETO AND 
            SCRIPT = 'BL001_BD_0500_STORED_PROCEDURES' AND 
            NUM_IDENT = @NUM_IDENT

    
    -- CHAMA A PROCEDURE QUE CAPTURA E EXIBE O ERRO OCORRIDO...    
    IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
        EXECUTE usp_GetErrorInfo  
    ELSE
    BEGIN
        -- EXIBE AS INFORMAÇÕES SOBRE O ERRO (SE NECESSÁRIO, DESABILITAR)
        PRINT 'Informações sobre o erro ocorrido'    
        PRINT '       ErrorMessage..:' + CONVERT(VARCHAR, ERROR_MESSAGE())
        PRINT '       ErrorNumber...:' + CONVERT(VARCHAR, ISNULL(ERROR_NUMBER(),0))
        PRINT '       ErrorSeverity.:' + CONVERT(VARCHAR, ISNULL(ERROR_SEVERITY(),0))
        PRINT '       ErrorState....:' + CONVERT(VARCHAR, ISNULL(ERROR_STATE(),0))
        PRINT '       ErrorProcedure:' + CONVERT(VARCHAR, ISNULL(ERROR_PROCEDURE(),0))
        PRINT '       ErrorLine.....:' + CONVERT(VARCHAR, ISNULL(ERROR_LINE(),0))    
        PRINT ''
    END

    -- MSG PADRÃO P/ O FRAMEWORK
    SET @MSG_ERRO = 'MESSAGE=' + ERROR_MESSAGE()    
    RAISERROR 20001 @MSG_ERRO

    -- SE NECESSÁRIO, CANCELA A TRANSAÇÃO...
    IF(@TRATA_ERRO = 1) 
    BEGIN
        IF( (XACT_STATE()) = -1 ) ROLLBACK TRAN
        PRINT 'MESSAGE=A transação atual foi cancelada !'        
    END    
    ELSE
    BEGIN
        IF( (XACT_STATE()) = 1 ) COMMIT TRAN
        PRINT 'MESSAGE=Apesar do erro, a transação atual foi confirmada !'
    END

    -- SE NECESSÁRIO, SAI DO SCRIPT VIA RETURN OU CONTINUA A EXECUÇÃO DAS OPERAÇÕES LOGO APÓS O END CATCH...
    IF(@CONTINUA_EXEC = 0) 
    BEGIN
        SET @MSG_ERRO = 'MESSAGE=O fluxo atual do script será interrompido !'
        PRINT @MSG_ERRO 
        RETURN
    END
    ELSE
    BEGIN
        SET @MSG_ERRO = 'MESSAGE=O fluxo atual do script continuará !'
        PRINT @MSG_ERRO 
    END
END CATCH;


/*
-- =====================================================================
-- Auditoria:
--
--    1) Verifica a criação do objeto;
--
-- =====================================================================
*/
-- INICIALIZA OS NOMES DOS OBJETOS DEPENDENTES
SET @OBJ_PROC   = ''
SET @OBJ_CAMPO  = ''
SET @OBJ_TIPO   = 'P'

IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE TYPE = @OBJ_TIPO AND NAME = @OBJ_PROC)
BEGIN
    SET @MSG_ERRO = 'MESSAGE=O objeto ' + @OBJ_PROC + ' não foi criado e o script será interrompido !'
    PRINT @MSG_ERRO 
    RETURN
END
ELSE
BEGIN
    SET @MSG_ERRO = 'MESSAGE=O objeto ' + @OBJ_PROC + ' foi criado com sucesso !'
    PRINT @MSG_ERRO 
END


--REGISTRA A EXECUÇÃO DO SCRIPT
--
IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE TYPE = 'U' AND NAME = 'DICMONITOR')
    UPDATE DICMONITOR 
    SET 
        HORA_FIM = GETDATE(), 
        STATUS = 'TERMINO', 
        MENSAGEM = '',
        LASTUPDATE = GETDATE()
    WHERE
        PROJETO = @NOME_PROJETO AND 
        SCRIPT = 'BL001_BD_0500_STORED_PROCEDURES' AND 
        NUM_IDENT = @NUM_IDENT
GO

[kuroi]

jefferson, você pode postar quais são exatamente as mensagens de erro q ele mostra nesses dois casos??

[Guest Jefferson Alves]

jefferson, você pode postar quais são exatamente as mensagens de erro q ele mostra nesses dois casos??

A primeira mensagem é a seguinte:

Msg 156, Level 15, State 1, Line 107

Incorrect syntax near the keyword 'PROCEDURE'.

Isso me obriga a deslocar a sentença responsável pela criação da stored proedure

para fora do bloco BEGIN TRY / BEGIN CATCH, algo que não gostaria pois o propósito

era justamente esse.

Após esta mudança, surge a seguinte mensagem de erro:

Msg 111, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 110

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Isso me obriga a colocar um GO antes do CREATE PROCEDURE que está fora do bloco. Com

isso, "quebro o script em 2 partes" e resulta na não identificação das variáveis

declaradas no arquivo e que são utilizadas dentro do bloco. Veja as mensagens agora:

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 32

Must declare the scalar variable "@TRATA_ERRO".

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 33

Must declare the scalar variable "@CONTINUA_EXEC".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 52

Must declare the scalar variable "@NOME_PROJETO".

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 74

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 75

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 78

Must declare the scalar variable "@TRATA_ERRO".

Msg 156, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 83

Incorrect syntax near the keyword 'ELSE'.

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 90

Must declare the scalar variable "@CONTINUA_EXEC".

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 92

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 93

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 98

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 99

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 113

Must declare the scalar variable "@OBJ_PROC".

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 114

Must declare the scalar variable "@OBJ_CAMPO".

Msg 137, Level 15, State 1, Procedure SP_NOME_PROCEDURE, Line 115

Must declare the scalar variable "@OBJ_TIPO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 117

Must declare the scalar variable "@OBJ_TIPO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 119

Must declare the scalar variable "@OBJ_PROC".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 120

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 125

Must declare the scalar variable "@OBJ_PROC".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 126

Must declare the scalar variable "@MSG_ERRO".

Msg 137, Level 15, State 2, Procedure SP_NOME_PROCEDURE, Line 140

Must declare the scalar variable "@NOME_PROJETO".

Ou seja, terei que mudar toda a estrutura deste arquivo e usar apenas RAISEERROR e

@@ROWSCOUNT etc. ?

[kuroi]

jefferson qual a versao do sql você esta usando??

realmente, você não vai conseguir criar a procedure dentro do try/catch assim. mas uma opcao seria você gravar o script de criacao da procedure num arquivo .sql separado. e ai na hora q precisar criar a procedure, você executa esse arquivo.

cara, não achei uma funcao pra executar o codigo do arquivo por script. tipo, acho q deve ter um modo mas não achei. mas sei q tem como você ler qualquer arquivo de texto. e ai você leria um arquivo com o codigo e executava.

aqui nesse link ele ensina como faz isso: http://www.simple-talk.com/sql/t-sql-progr...er-using-t-sql/

não sei q versao do sql você usa, mas já usei isso no sql 2005 e funcionou certinho, mas em outras versoes não sei se funciona, mas você pode tentar ai.

mas basicamente, você tem só q criar essa funcao no seu database:

Create Function [dbo].[ufsReadfileAsString]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS 
 Varchar(max)
AS
BEGIN

DECLARE  @objFileSystem int
        ,@objTextStream int,
		@objErrorObject int,
		@strErrorMessage Varchar(1000),
	    @Command varchar(1000),
		@Chunk Varchar(8000),
		@String varchar(max),
	    @hr int,
		@YesOrNo int

Select @String=''
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod   @objFileSystem  , 'OpenTextFile'
	, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
	BEGIN
	if @HR=0 Select @objErrorObject=@objTextStream, 
		@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
	if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

	IF @YesOrNo<>0  break
	if @HR=0 Select @objErrorObject=@objTextStream, 
		@strErrorMessage='reading from the output file "'+@filename+'"'
	if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Read', @chunk OUTPUT,4000
	SELECT @String=@string+@chunk
	end
if @HR=0 Select @objErrorObject=@objTextStream, 
	@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'


if @hr<>0
	begin
	Declare 
		@Source varchar(255),
		@Description Varchar(255),
		@Helpfile Varchar(255),
		@HelpID int

	EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
		@source output,@Description output,@Helpfile output,@HelpID output
	Select @strErrorMessage='Error whilst '
			+coalesce(@strErrorMessage,'doing something')
			+', '+coalesce(@Description,'')
	select @String=@strErrorMessage
	end
EXECUTE  sp_OADestroy @objTextStream
	-- Fill the table variable with the rows for your result set

	RETURN @string
END
[/codebox]
e ai você tem q dar permissao pra poder ler arquivos, é só rodar esse script:
[code]sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO[/code]

mas isso acho q so no 2005. veja mais informacoes sobre isso aqui: http://msdn.microsoft.com/en-us/library/ms191188.aspx



ai então, imagine q você salve o conteudo dessa procedure num arquivo chamado proc.sql:
[code]-- =============================================
    -- Author.....:
    -- Create date: 99/99/99
    -- Description:    
    -- =============================================
    CREATE PROCEDURE [dbo].[SP_NOME_PROCEDURE]
    (
        @PARAMETRO INT
    )
    AS
    BEGIN

        DECLARE
            @VARIAVEL    INT

        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Executa as operações
    END[/code]

ai você faria isso q você quer simplesmente assim:
[code]BEGIN TRY
    -- INICIA A TRANSAÇÃO E DEFINE O TRATAMENTO DE ERROS E A CONTINUIDADE NO SCRIPT...
    SET XACT_ABORT OFF;
    BEGIN TRAN     
    SET @TRATA_ERRO    = 1
    SET @CONTINUA_EXEC = 1

    -- INÍCIO DE EXECUÇÃO DAS OPERAÇÕES...
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    


    -- AQUI PARA CRIAR A PROCEDURE:
    DECLARE @var VARCHAR(8000)
    SELECT @var = dbo.ufsReadfileAsString ('C:\caminho\','proc.sql')
    EXEC (@var)


    
    SET ANSI_NULLS OFF
    SET QUOTED_IDENTIFIER OFF
    SET ANSI_PADDING OFF    

    -- FINALIZA A TRANSAÇÃO ATUAL...
    COMMIT TRAN
    PRINT 'MESSAGE=A transação atual foi concluída com sucesso !'
END TRY[/code]
veja ai se funciona, qualquer coisa poste.

[Jefferson Alves]

Olá Kuroi,

Desculpe-me pela demora no retorno, mas hoje está realmente muito complicado aqui. Comecei a ver a sua sugestão para a solução logo pela manhã e tive que parar. Ocorreram erros na criação daquela primeira stored procedure e alguns consegui contornar. Assim que tiver uma posição melhor te retorno. Hoje me ocorreu que também poderia montar uma string e executar tudo a partir do Execute talvez. A propósito, sempre me esqueço de citar informações importantes sobre versão e ambiente, no caso, estou usando o SQL 2005.

Jefferson

Editado Outubro 9, 2008 por Jefferson Alves

[kuroi]

Hoje me ocorreu que também poderia montar uma string e executar tudo a partir do Execute talvez.

opa, isso é verdade tb, as vezes a gente tenta o mais complicado pra fazer o mais simples. mas tente isso ai, qualquer coisa poste.

[Guest Jefferson Alves]

Bom dia Kuroi,

Mais uma vez desculpe-me pela demora no atraso do retorno. Hoje tive tempo de rever o material passado por você. Depois de ajustar o código daquela procedure inicial, implementei o resto e funcionou perfeitamente. É realmente muito fácil agora gerar um arquivo/processo de controle de execução de outros scripts.

Obrigado pela grande ajuda e se precisar de algo é só contatar-me.

Jefferson

jefferson.ralves@hotmail.com