Solved

SQL Exec Multiple Stored Procedures and Raiserror Try Catch?

Posted on 2013-10-26
6
1,030 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 27

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 27

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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 

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 27

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 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