Solved

Confirmation of SQL Transaction

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

9 Experts available now in Live!

Get 1:1 Help Now