Solved

Confirmation of SQL Transaction

Posted on 2013-02-04
4
347 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now