如何将 Try/Catch 添加到 SQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1480881/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a Try/Catch to SQL Stored Procedure
提问by Domnic
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 @@VOUCHER_NO=@PNO
--commit transaction
END
In this proc how can I handle try catch for exception?
在这个过程中，我如何处理 try catch 异常？
回答by Preet Sangha
See 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
回答by Remus Rusanu
Transact-SQL is a bit more tricky that C# or C++ try/catch blocks, because of the added complexity of transactions. A CATCH block has to check the xact_state() function and decide whether it can commit or has to rollback. I have covered the topic in my blog and I have an article that shows how to correctly handle transactions in with a try catch block, including possible nested transactions: Exception handling and nested transactions.
Transact-SQL 比 C# 或 C++ try/catch 块更棘手，因为增加了事务的复杂性。CATCH 块必须检查 xact_state() 函数并决定它是否可以提交或必须回滚。我在我的博客中介绍了这个主题，我有一篇文章展示了如何使用 try catch 块正确处理事务，包括可能的嵌套事务：异常处理和嵌套事务。
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
回答by ashleedawg
Error-Handling with SQL Stored Procedures
使用 SQL 存储过程进行错误处理
TRY/CATCHerror handling can take place either within or outside of a procedure (or both). The examples below demonstrate error handling in both cases.
TRY/CATCH错误处理可以发生在程序内部或外部（或两者）。下面的示例演示了两种情况下的错误处理。
If you want to experiment further, you can fork the queryon Stack Exchange Data Explorer.
如果你想进一步试验，你可以在 Stack Exchange Data Explorer上fork 查询。
(This uses a temporary stored procedure... we can't create regularSP's on SEDE, but the functionality is the same.)
（这使用了一个临时存储过程……我们无法在 SEDE 上创建常规SP，但功能是相同的。）
--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 1
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 2
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
Here's the result of running the above sql as-is:
这是按原样运行上述 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.
1Uncommenting the "additional error line" from the Stored Procedure's CATCHblock will produce:
1从存储过程的CATCH块中取消注释“附加错误行”将产生：
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
2Uncommenting the "additional error line" from the MAINprocedure will produce:
2从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)
Use a single procedure for error handling
使用单一过程进行错误处理
On topic of stored procedures and error handling, it can be helpful (and tidier) to use a single, dynamic, stored procedure to handle errors for multiple other procedures or code sections.
关于存储过程和错误处理的主题，使用单个动态存储过程来处理多个其他过程或代码段的错误会很有帮助（而且更整洁）。
Here's an example:
下面是一个例子：
--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
Example Output: (This query can be forked on SEDE here.)
示例输出：（此查询可以在此处在 SEDE 上分叉。）
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.
Documentation:
文档：
In the scope of a TRY/CATCHblock, the following system functions can be used to obtain information about the error that caused the CATCHblock to be executed:
在TRY/CATCH块的范围内，可以使用以下系统函数获取有关导致CATCH块执行的错误的信息：
ERROR_NUMBER()returns the number of the error.ERROR_SEVERITY()returns the severity.ERROR_STATE()returns the error state number.ERROR_PROCEDURE()returns the name of the stored procedure or trigger where the error occurred.ERROR_LINE()returns the line number inside the routine that caused the error.ERROR_MESSAGE()returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
ERROR_NUMBER()返回错误的编号。ERROR_SEVERITY()返回严重性。ERROR_STATE()返回错误状态编号。ERROR_PROCEDURE()返回发生错误的存储过程或触发器的名称。ERROR_LINE()返回导致错误的例程中的行号。ERROR_MESSAGE()返回错误消息的完整文本。文本包括为任何可替换参数提供的值，例如长度、对象名称或时间。
(Source)
（来源）
Note that there are two types of SQL errors: Terminaland Catchable. TRY/CATCHwill [obviously] only catch the "Catchable" errors. This is one of a number of ways of learning more about your SQL errors, but it probably the most useful.
请注意，有两种类型的 SQL 错误：Terminal和Catchable。 TRY/CATCH将 [显然] 仅捕获“可捕获”错误。这是了解更多有关 SQL 错误的多种方法之一，但它可能是最有用的方法。
It's "better to fail now" (during development) compared to later because, as Homer says. . .
与以后相比，“现在失败更好”（在开发期间），因为 正如荷马所说。. .
回答by Chagbert
yep - you can even nest the try catch statements as:
是的 - 您甚至可以将 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
回答by Harikesh Yadav
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 title=@title)>0)
BEGIN
SELECT 'record already existed.';
END
ELSE
BEGIN
if @id=0
begin
select @tempid =id from [xyz] where id=@id;
if @tempid is null
BEGIN
INSERT INTO xyz
(entrydate,updatedate)
VALUES
(GETDATE(),GETDATE())
SET @tempid=@@IDENTITY;
END
END
ELSE
BEGIN
set @tempid=@id
END
if @tempid>0
BEGIN
-- Updation of table begin--
UPDATE tab_questions
set title=@title, --0
tags=@tags, --1
category=@category, --2
ispoll=@ispoll, --3
descriptions=@descriptions, --4
status=@status, --5
WHERE id=@tempid ; --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;
