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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error handling in sub procedures

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 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

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).
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -