Как добавить хранимую процедуру Try/Catch to SQL

CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1]        
@GAD_COMP_CODE  VARCHAR(2) =NULL, 
@@voucher_no numeric =null output 
AS         
BEGIN  
    DECLARE @NUM NUMERIC 
    DECLARE @PNO  NUMERIC                               
    SET @PNO = 0 
    DECLARE @PNO1 NUMERIC
    SET @PNO1=0 

--  begin transaction 

    IF NOT EXISTS (select GLDC_NEXT_PRV_NO
               FROM   GLAS_FINANCIAL_DOCUMENTS          
                   WHERE  GLDC_COMP_CODE  = @GAD_COMP_CODE        
                   AND GLDC_DOC_CODE  = 'JV' )
    BEGIN
               RAISERROR ('Error in generating provision number..',16,1) 
               -- ROLLBACK TRANSACTION
    END
ELSE
SELECT @PNO=ISNULL(GLDC_NEXT_PRV_NO,0)+1
FROM   GLAS_FINANCIAL_DOCUMENTS          
WHERE  GLDC_COMP_CODE  = @GAD_COMP_CODE        
AND GLDC_DOC_CODE  = 'JV' 

UPDATE  GLAS_FINANCIAL_DOCUMENTS        
SET GLDC_NEXT_PRV_NO = @PNO         
WHERE  GLDC_COMP_CODE  = @GAD_COMP_CODE        
AND GLDC_DOC_CODE  = 'JV' 

set @@[email protected]    
--commit transaction 
END

В этом proc как я могу обработать try catch для исключения?

Ответы

Ответ 1

Смотрите TRY... CATCH (Transact-SQL)

 CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1]        
       @GAD_COMP_CODE  VARCHAR(2) =NULL, 
       @@voucher_no numeric =null output 
       AS         
   BEGIN  

     begin try 
         -- your proc code
     end try

     begin catch
          -- what you want to do in catch
     end catch    
  END -- proc end

Ответ 2

Transact-SQL немного сложнее в том, что С# или С++ try/catch блоки из-за сложности транзакций. Блок CATCH должен проверить функцию xact_state() и решить, может ли он совершить или должен выполнить откат. Я затронул тему в своем блоге, и у меня есть статья, в которой показано, как правильно обрабатывать транзакции с помощью блока catch try, включая возможные вложенные транзакции: Обработка исключений и вложенные транзакции.

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(),
                 @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end

Ответ 3

Обработка ошибок с помощью хранимых процедур SQL

TRY/CATCH Обработка ошибок может происходить как внутри, так и вне процедуры (или в обеих). Приведенные ниже примеры демонстрируют обработку ошибок в обоих случаях.

Если вы хотите поэкспериментировать дальше, вы можете разветкить запрос в Stack Exchange Обозреватель данных.

_{(При этом используется временная хранимая процедура... мы не можем создать обычный SP на SEDE, но функциональность та же.)}

--our Stored Procedure
create procedure #myProc as --we can only create #temporary stored procedures on SEDE. 
  begin
    BEGIN TRY
      print 'This is our Stored Procedure.'
      print 1/0                          --<-- generate a "Divide By Zero" error.
      print 'We are not going to make it to this line.'
    END TRY

    BEGIN CATCH
      print 'This is the CATCH block within our Stored Procedure:'
          + ' Error Line #'+convert(varchar,ERROR_LINE())
          + ' of procedure '+isnull(ERROR_PROCEDURE(),'(Main)')
      --print 1/0                        --<-- generate another "Divide By Zero" error.
        -- uncomment the line above to cause error within the CATCH ¹ 
    END CATCH
  end
go

--our MAIN code block:
BEGIN TRY
  print 'This is our MAIN Procedure.'
  execute #myProc  --execute the Stored Procedure
      --print 1/0                        --<-- generate another "Divide By Zero" error.
        -- uncomment the line above to cause error within the MAIN Procedure ²
  print 'Now our MAIN sql code block continues.'
END TRY

BEGIN CATCH
  print 'This is the CATCH block for our MAIN sql code block:'
          + ' Error Line #'+convert(varchar,ERROR_LINE())
          + ' of procedure '+isnull(ERROR_PROCEDURE(),'(Main)')
END CATCH

Вот результат запуска вышеуказанного sql как есть:

This is our MAIN Procedure.
This is our Stored Procedure.
This is the CATCH block within our Stored Procedure: Error Line #5 of procedure #myProc
Now our MAIN sql code block continues.

¹ Раскомментирование "дополнительной строки ошибки" из блока CATER хранимой процедуры приведет к:

This is our MAIN procedure.
This is our Stored Procedure.
This is the CATCH block within our Stored Procedure: Error Line #5 of procedure #myProc
This is the CATCH block for our MAIN sql code block: Error Line #13 of procedure #myProc

² Раскомментирование "дополнительной строки ошибки" из процедуры MAIN приведет к:

This is our MAIN Procedure.
This is our Stored Pprocedure.
This is the CATCH block within our Stored Procedure: Error Line #5 of procedure #myProc
This is the CATCH block for our MAIN sql code block: Error Line #4 of procedure (Main)

Используйте одну процедуру для обработки ошибок

Что касается хранимых процедур и обработки ошибок, может быть полезно (и более аккуратно) использовать одну динамическую хранимую процедуру для обработки ошибок для нескольких других процедур или разделов кода.

Вот пример:

--our error handling procedure
create procedure #myErrorHandling as
  begin
    print ' Error #'+convert(varchar,ERROR_NUMBER())+': '+ERROR_MESSAGE()  
    print ' occurred on line #'+convert(varchar,ERROR_LINE())
         +' of procedure '+isnull(ERROR_PROCEDURE(),'(Main)')
    if ERROR_PROCEDURE() is null       --check if error was in MAIN Procedure
      print '*Execution cannot continue after an error in the MAIN Procedure.'
  end
go

create procedure #myProc as     --our test Stored Procedure
  begin
    BEGIN TRY
      print 'This is our Stored Procedure.'
      print 1/0                       --generate a "Divide By Zero" error.
      print 'We will not make it to this line.'
    END TRY
    BEGIN CATCH
     execute #myErrorHandling
    END CATCH
  end
go

BEGIN TRY                       --our MAIN Procedure
  print 'This is our MAIN Procedure.'
  execute #myProc                     --execute the Stored Procedure
  print '*The error halted the procedure, but our MAIN code can continue.'
  print 1/0                           --generate another "Divide By Zero" error.
  print 'We will not make it to this line.'
END TRY
BEGIN CATCH
  execute #myErrorHandling
END CATCH

Пример вывода: _{^{(This query can be forked on SEDE here.)}}

This is our MAIN procedure.
This is our stored procedure.
 Error #8134: Divide by zero error encountered.
 occurred on line #5 of procedure #myProc
*The error halted the procedure, but our MAIN code can continue.
 Error #8134: Divide by zero error encountered.
 occurred on line #5 of procedure (Main)
*Execution cannot continue after an error in the MAIN procedure.

Документация:

В области действия блока TRY/CATCH следующие системные функции могут использоваться для получения информации об ошибке, которая привела к выполнению блока CATCH:

ERROR_NUMBER() возвращает номер ошибки.
ERROR_SEVERITY() возвращает серьезность.
ERROR_STATE() возвращает номер состояния ошибки.
ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера, где произошла ошибка.
ERROR_LINE() возвращает номер строки внутри подпрограммы, которая вызвала ошибку.
ERROR_MESSAGE() возвращает полный текст сообщения об ошибке. Текст включает значения, предоставленные для любых заменяемых параметров, таких как длины, имена объектов или время.

^(Source)

Обратите внимание, что существует два типа ошибок SQL: Terminal и Catchable. TRY/CATCH [очевидно] будет ловить только "Catchable" ошибки. Это один из способов узнать больше о ваших ошибках SQL, но он, вероятно, самый полезный.

"Лучше потерпеть неудачу сейчас" (во время разработки), чем позже, потому что, , как говорит Гомер., ,

Ответ 4

yep - вы можете даже вложить выражения try catch как:

BEGIN TRY
SET @myFixDte = CONVERT(datetime, @myFixDteStr,101)
END TRY
BEGIN CATCH
    BEGIN TRY
        SET @myFixDte = CONVERT(datetime, @myFixDteStr,103)
END TRY
BEGIN CATCH
    BEGIN TRY
        SET @myFixDte = CONVERT(datetime, @myFixDteStr,104)
    END TRY
    BEGIN CATCH
        SET @myFixDte = CONVERT(datetime, @myFixDteStr,105)
    END CATCH
END CATCH END CATCH

Ответ 5

Create Proc[usp_mquestions]  
( 
 @title  nvarchar(500),   --0
 @tags  nvarchar(max),   --1
 @category  nvarchar(200),   --2
 @ispoll  char(1),   --3
 @descriptions  nvarchar(max),   --4
)              
 AS  
 BEGIN TRY




BEGIN
DECLARE @message varchar(1000); 
DECLARE @tempid bigint; 

IF((SELECT count(id) from  [xyz] WHERE [email protected])>0)
BEGIN
SELECT 'record already existed.';
END
ELSE
BEGIN               


if @id=0 
begin 
select @tempid =id from [xyz] where [email protected];

if @tempid is null 
BEGIN 
        INSERT INTO xyz
        (entrydate,updatedate)
        VALUES
        (GETDATE(),GETDATE())

        SET @[email protected]@IDENTITY;
 END 
END 
ELSE 
BEGIN 
set @[email protected] 
END 
if @tempid>0 
BEGIN 

    -- Updation of table begin--


UPDATE  tab_questions
set [email protected], --0 
 [email protected], --1 
 [email protected], --2 
 [email protected], --3 
 [email protected], --4 
 [email protected], --5

WHERE [email protected] ; --9 ;


IF @id=0 
BEGIN 
SET @message= 'success:Record added successfully:'+ convert(varchar(10), @tempid)
END 
ELSE 
BEGIN 
SET @message= 'success:Record updated successfully.:'+ convert(varchar(10), @tempid)

END 
END 
ELSE 
BEGIN 
SET @message= 'failed:invalid request:'+convert(varchar(10), @tempid)
END 

END
END

END TRY
BEGIN CATCH
    SET @message='failed:'+ ERROR_MESSAGE();
END CATCH
SELECT @message;