Click here to Skip to main content
13,254,320 members (60,217 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

23K views
4 bookmarked
Posted 13 Nov 2011

SQL Server 2008 Transactions Usage Template

, 20 Nov 2011
Rate this:
Please Sign up or sign in to vote.
A starting point for your transaction-aware SQL Server scripts
I must admit I don't use Transactions that much, but the fact is that most of my stored procedures are atomic, i.e. although they may have a lot of code, only one data changing operation (INSERT | UPDATE | DELETE) is done, so there's no need to wrap it on a transaction.

Because I don't use them much, its not always clear to me what's the "best way" of using a transaction. Sure we all know the basics but:


  • Is the transaction always closed?
  • Are we handling the error that caused the transaction to rollback?
  • Are we accurately reporting the error to the caller?


To be able to always answer YES to all these questions without thinking much about it, my friend Rui Inacio dove into Google and came up with a template that can be used as a start point of all your transaction scripts.

BEGIN TRY
 BEGIN TRANSACTION

  -- ADD YOUR CODE HERE --

 IF @@TRANCOUNT > 0
 BEGIN
  COMMIT TRANSACTION;
 END
END TRY
BEGIN CATCH
 DECLARE @ErrorMessage VARCHAR(4000)

 SET @ErrorMessage = 'ErrorProcedure: ' + ISNULL(ERROR_PROCEDURE(), '') + ' Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) + ' Message: ' + ERROR_MESSAGE()

 IF @@TRANCOUNT > 0
 BEGIN
  ROLLBACK TRANSACTION;
 END

 RAISERROR (@ErrorMessage, 16, 1)
END CATCH


You may change the way you report the error inside the CATCH but for most cases this is what you need.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

AlexCode
Architect
Switzerland Switzerland
Senior IT Consultant working in Switzerland as Senior Software Engineer.

Find more at on my blog.

You may also be interested in...

Pro

Comments and Discussions

 
GeneralIs there any significance to doing the rollback before the r... Pin
dmjm-h6-Dec-11 8:35
memberdmjm-h6-Dec-11 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 20 Nov 2011
Article Copyright 2011 by AlexCode
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid