?
Solved

Confirmation of SQL Transaction

Posted on 2013-02-04
4
Medium Priority
?
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 36

Accepted Solution

by:
Miguel Oz earned 2000 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 36

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

762 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