The COMMIT TRANSACTION request has no correspondin

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

The COMMIT TRANSACTION request has no correspondin

Author

Topic

Swoosh
Starting Member

6 Posts

Posted - 2004-02-13 : 20:51:19

I'm getting this error when I run the following stored procedure. In the second insert statment, i've added a NULL to the query (which will make it fail) as I'm trying to test what will happen with the transaction, however, when I run this from Query Analyzer, it gives me the message I stated in the subject. Anyone know why?

ALTER PROCEDURE dbo.CreateUserFolder

@UserID int,
@FolderName varchar(255)

AS

-- Declare variables.
DECLARE @FolderID int
DECLARE @ReturnID int

-- Set default values.
SET @ReturnID = -1

BEGIN TRANSACTION
-- Create the folder record first.
INSERT INTO Folders VALUES (@FolderName, GETDATE(), GETDATE())
INSERT INTO FolderStorage VALUES (@UserID, NULL)

IF @@ERROR <> 0

/* ERROR OCCURRED, ROLLBACK THE INSERT */
ROLLBACK TRANSACTION

ELSE

SET @ReturnID = @@IDENTITY
COMMIT TRANSACTION

RETURN @ReturnID

Swoosh
Starting Member

6 Posts

Posted - 2004-02-13 : 21:16:22

Solved!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-15 : 22:30:11

Dontcha love it when someone just says they've solved the problem without saying what it was.
Here the commit is soutside the scope of the if block
But there is a further problem that @@error is not checked after the first insert
declare @error int

BEGIN TRANSACTION
-- Create the folder record first.
INSERT INTO Folders VALUES (@FolderName, GETDATE(), GETDATE())
select @error = @@error
if @error = 0
begin
INSERT INTO FolderStorage VALUES (@UserID, NULL)
select @error = @@error
end
IF @error <> 0
begin
/* ERROR OCCURRED, ROLLBACK THE INSERT */
ROLLBACK TRANSACTION
end
ELSE
begin
SET @ReturnID = @@IDENTITY
COMMIT TRANSACTION
end

I always try to put begin/end's around every if to be clear (try to anyway)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources