SQL Server - error after executing stored procedure

0
votes

When executing my stored procedure, why do I get the following error message?

Msg 266, Level 16, State 2, Procedure spAddCustomer, Line 0 [Batch Start Line 21]
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

Anything help, thanks.

Stored procedure code:

CREATE PROC spAddCustomer
    @FirstName VARCHAR = INPUT,
    @LastName VARCHAR = INPUT,
    @EmailAddress VARCHAR = INPUT,
    @PhoneNumber VARCHAR = INPUT
AS
BEGIN TRY
BEGIN TRANSACTION 
    INSERT INTO sales.CustomerPII (FirstName, LastName, EmailAddress, PhoneNumber)
    VALUES (@FirstName, @LastName, @EmailAddress, @PhoneNumber);

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
--Rows inserted still exist
--SELECT ERROR_NUMBER()
    --ROLLBACK TRANSACTION --Any transaction work will be undone
END CATCH;

Executed

EXEC spAddCustomer 'FirstTest', 'LastTest', 'EmailTest', 'AddressTest';
2
sql-servertsqlstored-procedurestransactions
Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use. For parameter, if you omit the explicit length, you get a parameter of exactly ONE character in length - which is typically not what you want ..... - marc_s
add BEGIN after AS and add END at the end of the store procedure. - iSR5

2 Answers

3
votes

Un-comment this line:

ROLLBACK TRANSACTION --Any transaction work will be undone
1
votes

Try setting XACT_ABORT ON in the stored procedure. When SET XACT_ABORT is ON and T-SQL statement raises a run-time error, SQL Server automatically rolls back the current transaction. Try it as follows: 

USE AdventureWorks2016CTP3
GO
CREATE PROC spAddCustomer
@FirstName varchar = INPUT,
@LastName varchar = INPUT,
@EmailAddress varchar = INPUT,
@PhoneNumber varchar = INPUT
AS

SET NOCOUNT ON 
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION 
INSERT INTO sales.CustomerPII (FirstName,LastName,EmailAddress,PhoneNumber)
VALUES(@FirstName, @LastName, @EmailAddress, @PhoneNumber);
COMMIT TRANSACTION
END TRY
BEGIN CATCH

END CATCH;