rollback not working - SQL Server Forums

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)

rollback not working

Author

Topic

brainst0rm
Starting Member

1 Post

Posted - 2007-07-02 : 09:28:23

Hi there. I have this query:

DECLARE Cursore CURSOR FOR SELECT * FROM diegotemp

DECLARE @Company AS VARCHAR(100)
DECLARE @Address1 AS VARCHAR(100)
DECLARE @Address2 AS VARCHAR(100)
DECLARE @Address3 AS VARCHAR(100)
DECLARE @County AS VARCHAR(100)
DECLARE @PostCode AS VARCHAR(100)
DECLARE @BusinessPhone AS NVARCHAR(100)
DECLARE @ConfettiRegion AS VARCHAR(100)

DECLARE @TownCounty INT

DECLARE @cpCompanyID INT
DECLARE @BranchID INT

DECLARE @intErrorCode INT

DECLARE @numberRows INT

OPEN Cursore

FETCH NEXT FROM Cursore INTO @Company, @Address1, @Address2, @Address3, @County, @PostCode, @BusinessPhone, @ConfettiRegion

BEGIN TRAN

WHILE @@FETCH_STATUS = 0
BEGIN
	
	INSERT INTO cpCompany(Name) VALUES (@Company)
	SET @cpCompanyID=(SELECT @@IDENTITY)
	SELECT @intErrorCode = @@ERROR
	IF (@intErrorCode <> 0)	GOTO PROBLEM
	SET @numberRows = @@ROWCOUNT 
	PRINT @numberRows
	
	
	INSERT INTO cpBranch(CompanyID,Name,WhenUpdated,WhenCreated) VALUES (@cpCompanyID,@Company,'01/01/1900 00:00:00','01/01/1900 00:00:00')
	SET @BranchID=(SELECT @@IDENTITY)
	SELECT @intErrorCode = @@ERROR
	IF (@intErrorCode <> 0)	GOTO PROBLEM

	
	SET @TownCounty=(SELECT id FROM geArea WHERE name = @county)
	INSERT INTO cpBranchInfo(BranchID,AddressLine1,AddressLine2,AddressLine3,PostalCode,County,Country,Telephone) VALUES (@BranchID,@Address1,@Address2,@Address3,@PostCode,@TownCounty,'222',@BusinessPhone)
	SELECT @intErrorCode = @@ERROR
	IF (@intErrorCode <> 0)	GOTO PROBLEM
	

	FETCH NEXT FROM Cursore INTO @Company, @Address1, @Address2, @Address3, @County, @PostCode, @BusinessPhone, @ConfettiRegion
END

COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

CLOSE Cursore
DEALLOCATE Cursore

Which basically take some infos from a temp table (diegotemp), and put them in the appropriate ones. I have a problem, the rollback seems to do not work correctly, can you help me please?

Thanks. Diego.

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 09:37:29

"I have a problem, the rollback seems to do not work correctly"

The code looks OK, what problem are you seeing?

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-02 : 10:27:10

These pieces of code are obviously wrong:

INSERT INTO cpCompany(Name) VALUES (@Company)
SET @cpCompanyID=(SELECT @@IDENTITY)
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0)	GOTO PROBLEM

INSERT INTO cpBranch(CompanyID,Name,WhenUpdated,WhenCreated) VALUES (@cpCompanyID,@Company,'01/01/1900 00:00:00','01/01/1900 00:00:00')
SET @BranchID=(SELECT @@IDENTITY)
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0)	GOTO PROBLEM

If you want to capture @@ERROR, you must do it on the first statement after your INSERT.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 10:46:39

Ah, didn't spot that.

Probably need to also change:

INSERT INTO cpBranch(CompanyID,Name,WhenUpdated,WhenCreated) VALUES (@cpCompanyID,@Company,'01/01/1900 00:00:00','01/01/1900 00:00:00')
SELECT @intErrorCode = @@ERROR, @BranchID=@@IDENTITY

However, you should be using Scope_Identity() instead of @@IDENTITY. There is a risk that @@IDENTITY will NOT give you the ID you are expecting.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources