CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Gfraser
Gfraser

Reputation: 15

Receiving an error: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION in SQL Script

Was trying to perform an update to a database for an application, and then SQL server threw me a Commit has no corresponding being transaction. It was kind enough to point me to the procedure but I cannot seem to find what the issue is.

This is my code:

ALTER PROCEDURE [dbo].[sp_tblUser_Update] @UserID             INTEGER,
                                          @UserName           NVARCHAR(20),
                                          @Password           NVARCHAR(10),
                                          @Yard               NVARCHAR(50),
                                          @NewRole            NVARCHAR(50),
                                          @FullName           NVARCHAR(50),
                                          @Email              NVARCHAR(50),
                                          @BCConEmails        BIT,
                                          @CrewStatusReadOnly BIT
AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      -- Insert statements for procedure here
      UPDATE tblUser
      SET    UserName = @UserName,
             UserPassword = @Password,
             Yard = @Yard,
             Fullname = @FullName,
             EmailAddress = @Email,
             NewRole = @NewRole,
             BCConEmails = @BCConEmails,
             CrewStatusReadOnly = @CrewStatusReadOnly
      WHERE  ( UserID = @UserID )
  END

GO

COMMIT

Upvotes: 1

Views: 12671

Answers (2)

ram
ram

Reputation: 1

begin tran
delete from Customerages 
where Age=25
commit;
begin tran
delete from Customerages 
where Age=25
rollback;

Upvotes: -1

talegna
talegna

Reputation: 2403

You don't need the COMMIT line as the END in your code is what terminates the previous BEGIN. If you want to add a transaction you should put a BEGIN TRANSACTION before the update statement and move the COMMIT before the END

Upvotes: 3

Related Questions