Solved

SQL Exec Multiple Stored Procedures and Raiserror Try Catch?

Posted on 2013-10-26
6
982 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
  • 3
  • 3
6 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:WorknHardr
Comment Utility
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
Comment Utility
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
Comment Utility
cool, thx
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now