SQL Server - error after executing stored procedure

Question

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';

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
– marc_s, Commented Nov 16, 2018 at 21:56
add BEGIN after AS and add END at the end of the store procedure. — iSR5
– iSR5, Commented Nov 16, 2018 at 23:27

Pittsburgh DBA · Accepted Answer · 2018-11-16 23:56:50Z

3

Un-comment this line:

ROLLBACK TRANSACTION --Any transaction work will be undone

edited Nov 16, 2018 at 23:56

Pittsburgh DBA

6,8042 gold badges44 silver badges71 bronze badges

answered Nov 16, 2018 at 21:23

kgzdev

2,9312 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pittsburgh DBA Over a year ago

I agree with @ikram. It appears that execution is going into the CATCH block, but no action is being taken there. You may also consider re-throwing the exception here, or logging. As written, it will roll back silently.

lije · Accepted Answer · 2018-11-19 13:52:01Z

1

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;

edited Nov 19, 2018 at 13:52

answered Nov 16, 2018 at 21:30

lije

4203 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL Server - error after executing stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related