Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-21 : 13:53:59
|
Hi, I am using the following flow to handle errors in procedures. Please check the code below, proc P1 calls a sub-proc childProc and CompileQuery. What if there is an error in any of the child procedures, I want the processing to stop there and then, rollback and log the error as being done by proc. sp_LogError. In my case errors are being trapped by child procs. themselves and not the catch of the parent.Will this work well? or is there a better way of doing this.Also, why does not the control returns back to the parent and why is raisError not firing? I have not included the definition of sp_LogError here.CREATE PROC P1ASbegin BEGIN TRY BEGIN TRAN --CALL TO A SUB PROCEDURE CHILDPROC 100 --assume this is a dynamic query DECLARE @sql varchar(max) = 'select 1 from table1' -- a procedure just to execute dynamic query using sp_executesql Exec CompileDynamicQuery(@sql) IF @@trancount>0 and xact_State=1 COMMIT END TRYENDBEGIN CATCH IF @@trancount>0 and xact_State=1BEGIN print 'error occurred' ROLLBACK sp_LogError RAISERROR ('ERROR',16,1)END END CATCHCREATE PROC CHILDPROC @a intASbegin BEGIN TRY BEGIN TRAN --assume this is a dynamic query DECLARE @sql varchar(max) = 'select 1 from table1' Exec CompileDynamicQuery(@sql) COMMIT END TRYENDBEGIN CATCH IF @@trancount>0 and xact_State=1BEGIN print 'error occurred' ROLLBACK sp_LogError RAISERROR ('ERROR',16,1)END END CATCHCREATE PROC CompileDynamicQuery@str VARCHAR(MAX)ASbegin BEGIN TRY BEGIN TRAN EXEC sp_executeSql @str COMMIT END TRYENDBEGIN CATCH IF @@trancount>0 and xact_State=1BEGIN print 'error occurred' ROLLBACK sp_LogError RAISERROR ('ERROR',16,1)END END CATCH--------------------Rock n Roll with SQL |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-21 : 14:58:57
|
There are several syntax errors in the sample code you posted (e.g., it is RAISERROR, not RAISEERROR, there should be nothing between END TRY and BEGIN CATCH statements etc.).Regardless, the pattern you want to use for a single stored procedure/code block is on this page: http://technet.microsoft.com/en-us/library/ms175976(v=sql.100).aspx Look for example C.When you have nested stored procedures, you can use that pattern in the outermost stored procedure. Errors raised in the inner stored procedures will bubble up to the catch block in the outermost procedure.Alternatively, you can catch the error in each stored proc (and rethrow them if you like if you are using SQL 2012). |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-21 : 15:10:41
|
Thanks James and sorry about the typo. Typed all this here and copied pasted the rest without checking in SSMS. I am going through the link and it does answer at least one question, answer is:"If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block."So now I understand why control is not returning to the parent. And so I believe raiserror in such cases does not work at all when put in the child procedures.--------------------Rock n Roll with SQL |
|
|
|
|
|
|
|