?
Solved

SQL Exec Multiple Stored Procedures and Raiserror Try Catch?

Posted on 2013-10-26
6
Medium Priority
?
1,047 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 2000 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 2000 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 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