Solved

Confirmation of SQL Transaction

Posted on 2013-02-04
4
350 Views
Last Modified: 2013-02-04
Hi,

I have the code below where I am trying to use an sql transaction.
I would just like confirmation that I have got it right or whether the transaction should actually wrap up the whole procedure!

Thanks

BEGIN


      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

     DECLARE @numOccs As  Integer
        SELECT @numOccs = Count(emailAddress)
          From tbl_EmailAddresses
         Where emailAddress = @emailAddress
         
        If isnull(@numOccs, 0) > 0
        BEGIN
          SET @ReturnValue =  '0'
        END
        ELSE
            BEGIN
            BEGIN TRAN TM
                  BEGIN TRY
                        COMMIT TRAN TM
                              INSERT INTO tbl_EmailAddresses
                              (emailAddress)
                              VALUES (@emailAddress)
                              SET @ReturnValue = '1'
                              END TRY
                              BEGIN CATCH
                  ROLLBACK TRAN TM
                  SET @ReturnValue = 'error' ;
                  END CATCH
            End
END
0
Comment
Question by:Soluga
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Miguel Oz earned 500 total points
ID: 38850632
Commit must be at the end of sql operation.
BEGIN


     SET NOCOUNT ON;

     DECLARE @numOccs As  Integer
        SELECT @numOccs = Count(emailAddress) 
          From tbl_EmailAddresses 
         Where emailAddress = @emailAddress
          
        If isnull(@numOccs, 0) > 0
        BEGIN
          SET @ReturnValue =  '0'
        END 
        ELSE
            BEGIN
            BEGIN TRAN TM
                  BEGIN TRY
                             INSERT INTO tbl_EmailAddresses
                              (emailAddress)
                              VALUES (@emailAddress)
                              SET @ReturnValue = '1'
                              COMMIT TRAN TM
                   END TRY
                   BEGIN CATCH
                  ROLLBACK TRAN TM
                  SET @ReturnValue = ERROR_NUMBER() ;
                  END CATCH
            End
END

Open in new window

0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38850633
Hi,

The commit statement should be after the insert.


Giannis
0
 
LVL 1

Author Closing Comment

by:Soluga
ID: 38850637
Thanks, didn't think it looked quite right.
0
 
LVL 35

Expert Comment

by:Miguel Oz
ID: 38850641
Just to clarify my previous post. I use  ERROR_NUMBER()  to provide the error number.
but SQL Server 2008  has a  set of  error functions such as:
ERROR_SEVERITY() Provides the severity of the errorERROR_STATE() Provides the state code of the error. This refers to the cause of the error.
ERROR_PROCEDURE() Returns the name of a stored procedure or trigger that caused the error.
ERROR_LINE() Returns the line number that caused the error.
ERROR_MESSAGE() Returns the actual text message describing the error.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loops and updating in SQL Query 9 53
Where is this file? 3 25
Change to file doesn't show up 16 51
Using SQL*PLUS issue with where statement with trunc function 3 27
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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