SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error handling in sub procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rocknpop
Posting Yak Master

169 Posts

Posted - 05/21/2013 :  13:53:59  Show Profile  Reply with Quote
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 P1
AS
begin
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 TRY
END
BEGIN CATCH

IF @@trancount>0 and xact_State=1
BEGIN
print 'error occurred'
ROLLBACK
sp_LogError
RAISERROR ('ERROR',16,1)
END

END CATCH

CREATE PROC CHILDPROC
@a int
AS
begin
BEGIN TRY
BEGIN TRAN

--assume this is a dynamic query
DECLARE @sql varchar(max) = 'select 1 from table1'
Exec CompileDynamicQuery(@sql)

COMMIT

END TRY
END
BEGIN CATCH

IF @@trancount>0 and xact_State=1
BEGIN
print 'error occurred'
ROLLBACK
sp_LogError
RAISERROR ('ERROR',16,1)
END

END CATCH


CREATE PROC CompileDynamicQuery
@str VARCHAR(MAX)
AS
begin
BEGIN TRY
BEGIN TRAN

EXEC sp_executeSql @str

COMMIT

END TRY
END
BEGIN CATCH

IF @@trancount>0 and xact_State=1
BEGIN
print 'error occurred'
ROLLBACK
sp_LogError
RAISERROR ('ERROR',16,1)
END

END CATCH

--------------------
Rock n Roll with SQL

Edited by - rocknpop on 05/21/2013 15:16:56

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 05/21/2013 :  14:58:57  Show Profile  Reply with Quote
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).
Go to Top of Page

rocknpop
Posting Yak Master

169 Posts

Posted - 05/21/2013 :  15:10:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000