Geek Answers Handbook

This good write transaction in a stored procedure

This is the first time I use transactions, and I'm just surprised that I am doing it right. Do I have to change anything? I am inserting a message (wisp). When inserting a message, I need to generate an identifier in the commentableEntity table and insert this identifier in the wisp table.

ALTER PROCEDURE [dbo].[sp_CreateWisp] @m_UserId uniqueidentifier, @m_WispTypeId int, @m_CreatedOnDate datetime, @m_PrivacyTypeId int, @m_WispText nvarchar(200) AS BEGIN TRANSACTION DECLARE @wispId int INSERT INTO dbo.tbl_Wisps (UserId,WispTypeId,CreatedOnDate,PrivacyTypeId,WispText) VALUES (@m_UserId,@m_WispTypeId,@m_CreatedOnDate,@m_PrivacyTypeId,@m_WispText) if @@ERROR <> 0 BEGIN ROLLBACK RAISERROR ('Error in adding new wisp.', 16, 1) RETURN END SELECT @wispId = SCOPE_IDENTITY() INSERT INTO dbo.tbl_CommentableEntity (ItemId) VALUES (@wispId) if @@ERROR <> 0 BEGIN ROLLBACK RAISERROR ('Error in adding commentable entity.', 16, 1) RETURN END DECLARE @ceid int select @ceid = SCOPE_IDENTITY() UPDATE dbo.tbl_Wisps SET CommentableEntityId = @ceid WHERE WispId = @wispId if @@ERROR <> 0 BEGIN ROLLBACK RAISERROR ('Error in adding wisp commentable entity id.', 16, 1) RETURN END COMMIT

Using try / catch based on @gbn answer:

 ALTER PROCEDURE [dbo].[sp_CreateWisp] @m_UserId uniqueidentifier, @m_WispTypeId int, @m_CreatedOnDate datetime, @m_PrivacyTypeId int, @m_WispText nvarchar(200) AS SET XACT_ABORT, NOCOUNT ON DECLARE @starttrancount int BEGIN TRY SELECT @starttrancount = @@TRANCOUNT IF @starttrancount = 0 BEGIN TRANSACTION DECLARE @wispId int INSERT INTO dbo.tbl_Wisps (UserId,WispTypeId,CreatedOnDate,PrivacyTypeId,WispText) VALUES (@m_UserId,@m_WispTypeId,@m_CreatedOnDate,@m_PrivacyTypeId,@m_WispText) SELECT @wispId = SCOPE_IDENTITY() INSERT INTO dbo.tbl_CommentableEntity (ItemId) VALUES (@wispId) DECLARE @ceid int select @ceid = SCOPE_IDENTITY() UPDATE dbo.tbl_Wisps SET CommentableEntityId = @ceid WHERE WispId = @wispId IF @starttrancount = 0 COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() <> 0 AND @starttrancount = 0 ROLLBACK TRANSACTION RAISERROR ('Error in adding new wisp', 16, 1) END CATCH GO
+4
source share
3 answers

You are using TRY / CATCH with SQL Server 2005+

Your rollback goes into the CATCH block, but your code looks pretty different (using SCOPE_IDENTITY (), etc.). I would also use SET XACT_ABORT, NOCOUNT ON

This is my template: Nested stored procedures containing the TRY CATCH ROLLBACK template?

Edit:

  • This allows nested transactions according to DeveloperX answer
  • This template also allows for higher-level transactions according to Randy's comment.
+6
source

I think that it is not very good all the time, but if you want to use several stored procedures at the same time, this is not good, because each stored procedure processes the transaction independently

but in this case you should use the catch try block, handling exceptions, and prevent the transaction from opening when the exception fails

+1
source

I have never considered it a good idea to place transactions in a stored procedure. I think it is much better to start a transaction at a higher level in order to better coordinate the calls of several databases (for example, a stored procedure) and treat them as a single transaction.

+1
source

More articles:


All Articles