[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Rolling back changes (Serializable) within Stored proc

Hi All,

I have a main stored procedure that calls various other stored proc's during it's execution. I'm trying to write some sort of logic so that if the main sp or any subsequent sp's called within it fails it should rollback the entire transaction.

Any sample code would be appreciated.

Thanks.
0
Samoin
Asked:
Samoin
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Potentially stupid question>  Have you tried using a transaction?
BEGIN TRY
  BEGIN TRAN tr
  -- do your stuff here

  -- If code execution makes it here, then good to go. 
  COMMIT TRAN tr
END TRY

BEGIN CATCH
  -- If code execution makes it here then there was a failure, kill the transaction
  ROLLBACK TRAN tr
END CATCH

Open in new window

0
 
SamoinAuthor Commented:
Hi Jim,

Let me give it try and I will let you know shortly.

Thanks.
0
 
aneesa83Commented:
CREATE PROC TEST1

@TEST BIGINT
AS
BEGIN
SELECT @TEST+3/10
SELECT @TEST
END
;

CREATE PROC TEST2
@VAR VARCHAR
AS
BEGIN
BEGIN TRAN
EXEC TEST1 @VAR
IF( @@ERROR=0)
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK  
END
END

EXEC TEST2 A
0
 
SamoinAuthor Commented:
Hi Jim,

I implemented your logic and it works.

Regards,
Samoin
0
 
SamoinAuthor Commented:
Hi aneesa83

I would prefer Transaction within a TRY....CATCH block rather then just rolling back the transaction.

Regards,
Samoin
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now