Confirmation of SQL Transaction

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
LVL 1
SolugaAsked:
Who is Participating?
 
Miguel OzConnect With a Mentor Software EngineerCommented:
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
 
Ioannis ParaskevopoulosCommented:
Hi,

The commit statement should be after the insert.


Giannis
0
 
SolugaAuthor Commented:
Thanks, didn't think it looked quite right.
0
 
Miguel OzSoftware EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.