Call stored procedure inside other sp

CREATE PROCEDURE spTestTran_CallSp
AS
DECLARE @RetCode INT,
 @SQL_Error INT
 
— Start transaction
BEGIN TRANSACTION
IF
@@ERROR != 0
 RETURN -1
 
— operation1
IF @@ERROR != 0
 GOTO Rollback_Point
 
— Call stocata 2
EXEC @RetCode = spTestTran_Savepoint
SET @SQL_Error = @@ERROR
IF @SQL_Error != 0 OR @RetCode != 0
 GOTO Rollback_Point
 
COMMIT TRAN
RETURN 0 — normal exit
 
Rollback_Point:
ROLLBACK TRANSACTION — undo any changes
RETURN -1 — return with error

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s