Soluga
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, didn't think it looked quite right.
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.
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.
The commit statement should be after the insert.
Giannis