Solved

SQL Exec Multiple Stored Procedures and Raiserror Try Catch?

Posted on 2013-10-26
6
1,013 Views
Last Modified: 2013-10-27
I have a 'parent' SP which calls several other SP's.
1. I need to Halt or Continue the master SP contingent on each 'child' execution status.  
2. I need to Catch any 'Child' errors
3. I need each child SP still have ability to run stand-alone

[SP_Parent]
...
AS

BEGIN TRY      

      Declare @_OrgId int;
      Declare @_DealerId  int;
      Declare @_LocId int;

      --[ORGANIZATION TABLE]                  
      BEGIN            
            BEGIN TRAN
                  Exec sp_Insert_Organizations @Name,  @Id = @_OrgId Output;
            COMMIT TRAN
      END      
            
      --[DEALERS TABLE]            
      BEGIN            
            BEGIN TRAN
                  Exec sp_Insert_Dealers @_OrgId, @Id = @_DealerId Output;
            COMMIT TRAN
      END

      --[LOCATIONS TABLE]
      BEGIN            
            BEGIN TRAN
                  Exec sp_Insert_Locations @_OrgId, @_DealerId ,  @Id = @_LocId Output;
            COMMIT TRAN
      END
            

The following SP uses Transaction, Raiserror and SP_ErrorLog table.
  1. I want to Bubble-Up errors/status to the 'Parent SP' above
      - I don't think using a Try/Catch here is a good idea
  2. I still want to track errors if this SP is called by some other program
      - I use the SP_ErrorLog table to keep any errors this SP causes

[SP_ORGANIZATIONS]
ALTER PROCEDURE [dbo].[sp_Insert_Organizations]
(            
      @OrgName      nvarchar(255),
      @Id                  int OUTPUT
)
AS
BEGIN
      IF NOT EXISTS(SELECT NULL FROM tbl_Organization WHERE OrgName = @OrgName)                  
            BEGIN            
                  BEGIN TRAN      
                        INSERT INTO Organization (OrgName) VALUES (@OrgName)      
                        SET @Id = Scope_Identity()
                  COMMIT TRAN
            END
      ELSE            
            RAISERROR ('Organization ID already exists in Dealers table', -- Message text
                              16, -- Severity.
                              1 -- State.
                              );

            ROLLBACK TRAN

            DECLARE @ErrorMsg VARCHAR(MAX), @ErrorNumber INT, @ErrorProc sysname, @ErrorLine INT

            SELECT @ErrorMsg = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorProc = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE();
                  
            INSERT INTO SP_ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine)
            VALUES (@ErrorMsg, @ErrorNumber, @ErrorProc, @ErrorLine)
END
0
Comment
Question by:WorknHardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39603759
The child procedure: the way your code is written there is nothing to rollback if the @OrgName already exists in the table because it avoids the insert in this case. You don't need that rollback but you can use the RAISERROR if that is what you want.

In the parent procedure: there is no need for BEGIN TRAN ... COMMIT TRAN because the transaction happens in the child procedure. Beside that any procedure in SQL server is treated as an implicit transaction which applies to all procedures called within it. If there will be an error the execution stops at the very statement that caused it and anything done before it, including in the parent procedure, will be automatically rolled back. It is superfluous to add those explicit transactions.

If you want to handle errors you should use TRY... CATCH blocks with COMMIT in the TRY branch and with ROLLBACK in the CATCH. However in this case you don't need to do that.

This should do it:
[SP_Parent]
...
AS

	Declare @_OrgId int;
	Declare @_DealerId  int;
	Declare @_LocId int;

	--[ORGANIZATION TABLE]                  
	Exec sp_Insert_Organizations @Name,  @Id = @_OrgId Output;
		
	--[DEALERS TABLE]            
	Exec sp_Insert_Dealers @_OrgId, @Id = @_DealerId Output;

	--[LOCATIONS TABLE]
	Exec sp_Insert_Locations @_OrgId, @_DealerId ,  @Id = @_LocId Output;

	
--[SP_ORGANIZATIONS]
ALTER PROCEDURE [dbo].[sp_Insert_Organizations]
(            
      @OrgName      nvarchar(255), 
      @Id                  int OUTPUT
)
AS
BEGIN

      IF NOT EXISTS(SELECT NULL FROM tbl_Organization WHERE OrgName = @OrgName)                  
            BEGIN            
				INSERT INTO Organization (OrgName) VALUES (@OrgName)      
				SET @Id = Scope_Identity()
            END
      ELSE            
            RAISERROR ('Organization ID already exists in Dealers table', -- Message text
                              16, -- Severity.
                              1 -- State.
                              );
	DECLARE @ErrorMsg VARCHAR(MAX), @ErrorNumber INT, @ErrorProc sysname, @ErrorLine INT 

	SELECT @ErrorMsg = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorProc = ERROR_PROCEDURE(), @ErrorLine = ERROR_LINE();
		  
	INSERT INTO SP_ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine)
	VALUES (@ErrorMsg, @ErrorNumber, @ErrorProc, @ErrorLine)
END

Open in new window

0
 

Author Comment

by:WorknHardr
ID: 39603928
Understood! I do C# programming and use ArgumentException in a Child method and Try/Catch only in the Parent method to display the Child error. It's a bubble-up pattern which works quite well. This is my goal in SQL syntax.
Okay, let's say I use a parent output parameter like: @Status string OUTPUT,
Q. How do I catch the Child RAISERROR in the Parent OUTPUT @Status?
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 500 total points
ID: 39604181
You can have multiple output parameters for every error detail like number, message, severity, etc. The parameters you insert into the log in the child procedure could very well be output parameters, which would make the values available in the parent procedure.
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:WorknHardr
ID: 39604242
Okay, I'm still using the RAISERROR to give the 'SP_ErrorLog' info to insert.

Q. How about this syntax to bubble-up errors from child to parent?

[SP_Child]
@OrgName      nvarchar(255),
@Id                   int OUTPUT,
@Error             nvarchar(255) OUTPUT
)
AS
BEGIN
      IF NOT EXISTS(...)                  
            BEGIN            
                  INSERT INTO Organization (OrgName) VALUES (@OrgName)      
                  SET @Id = Scope_Identity()
            END
      ELSE            
            RAISERROR ('Organization ID already exists in Dealers table', -- Message text
            @Error = RAISERROR


[SP_Parent]
@Id                  int OUTPUT,
@Error             nvarchar(255) OUTPUT
AS
      Exec sp_Insert_Organizations @Name, @Id = @Id OUTPUT,  @Error = @Status OUTPUT;
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39604266
That should do it. In the parent procedure you don't need to have output parameters if you don't have to return the values in the calling code of the parent.
0
 

Author Closing Comment

by:WorknHardr
ID: 39604338
cool, thx
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question