Solved

Transaction rollback with WHILE

Posted on 2006-06-14
6
724 Views
Last Modified: 2012-05-05
This is my SQL code:
<code>
DECLARE @intErrorCode INT
DECLARE @StartI int
DECLARE @StopI int
DECLARE @CurrentI int


set @StopI = 10
set @StartI = 0

set @CurrentI = @StartI


BEGIN TRAN

WHILE (@StopI > @CurrentI)
BEGIN

--DECLARE @CurrentI int
--SET @CurrentI = 3
    insert into [user]
      (username)
      values ('joeY' + (CAST(@CurrentI as Varchar(4))))
print 'added '+ ('joeY' + (CAST(@CurrentI as Varchar(4))))

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0)
     BEGIN
      ROLLBACK TRAN
      GOTO PROBLEM
     END

SET @CurrentI = (@CurrentI + 1) --Loop

END --WHILE

COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
--DECLARE @CurrentI int
--SET @CurrentI = 3
PRINT 'Error occurred at ' + (CAST(@CurrentI as Varchar(4)))

END

</code>
I created joeY4 to create a problem record so it will rollback
Everything passed with a hitch,

I expected it to rollback in case the user joeY4 exists.
I expect it not to create users joeY0, joeY1,joeY2,  joeY3  , joeY5, joeY6, joeY7 - 9

This is what the SQL Query analyzer gives me :
==============================

Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY0
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY1
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY2
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY3
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY4
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY5
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY6
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY7
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY8
Server: Msg 2627, Level 14, State 2, Procedure DD_DD, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'User'.
The statement has been terminated.
added joeY9

--------- since i have a trigger on this table

when i run it again i get

Server: Msg 2627, Level 14, State 2, Procedure AddUserToOldDB, Line 24
Violation of UNIQUE KEY constraint 'UniqueUserName'. Cannot insert duplicate key in object 'Users'.
The statement has been terminated.

'AddUserToOldDB' is my trigger.


But  in the user table i get.
joeY0
joeY1
joeY2
joeY3
joeY4
joeY5
joeY6
joeY7
joeY8
joeY9

why doesn't the transaction stop my WHILE Loop ?
how can i stop my While loop with the error.
Why doesn't the Transaction rollback and leave me with a single joeY4 record that i created ?

Thankyou kindly.

Mickey
0
Comment
Question by:interlect
6 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16905363
look up BREAK
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16905404
DECLARE @intErrorCode INT
DECLARE @StartI int
DECLARE @StopI int
DECLARE @CurrentI int
DECLARE @Rows  int --------for rowcount

set @StopI = 10
set @StartI = 0

set @CurrentI = @StartI


BEGIN TRAN

WHILE (@StopI > @CurrentI)
BEGIN

--DECLARE @CurrentI int
--SET @CurrentI = 3
    insert into [user]
     (username)
     values ('joeY' + (CAST(@CurrentI as Varchar(4))))
print 'added '+ ('joeY' + (CAST(@CurrentI as Varchar(4))))

    SELECT @intErrorCode = @@ERROR, @RowCount = @@ROWCOUNT  -----
    IF (@intErrorCode <> 0) OR @RowCount <>1
     BEGIN
     ROLLBACK TRAN
     GOTO PROBLEM
     END

0
 

Author Comment

by:interlect
ID: 16909694
I tried the rowcount, and no go.
it doesn't do anything at all.

Mickey
0
 

Author Comment

by:interlect
ID: 16912742
Ok, this is the sulution.

It's clumsy,
but it has two great advantages:

I have a trigger that runs to anotherDB and adds new users there too, I could not get THAT db to remotely allow me to rollback the trigger.

removing and returning the trigger is possible, but i dont like it.

this is the simple solution.

first check if you're gonna make it. if so, run.

Have a great day.

Mickey
drop PROC AddUsersIncremental_sp
GO
CREATE   PROC AddUsersIncremental_sp
(
      @prefix varchar(100),
      @StartI int,
      @StopI int
)
AS
DECLARE @CurrentI int
DECLARE @Rows  int --------for rowcount

--set @StopI = 9
--set @StartI = 0
--set @prefix = 'joeY'
set @CurrentI = @StartI
--DECLARE @vals varchar(3000)
declare @sql nchar(3000)

BEGIN TRAN

WHILE (@StopI >= @CurrentI)
BEGIN

--declare @CurrentI int            ; set @CurrentI = 4
--declare @prefix varchar(100)      ; set @prefix = 'joe'
--declare @sql nchar(3000)

 SELECT  @Rows = count(*)  
           FROM [user]  
           WHERE  
        username  = @prefix + CAST (@CurrentI as varchar(4) )

      if (@Rows > 0 )
      BEGIN
--            BREAK
        GOTO PROBLEM
      END

      SET @CurrentI = (@CurrentI + 1) --Loop
END --WHILE
--Now for the real thing
set @CurrentI = @StartI
WHILE (@StopI >= @CurrentI)
BEGIN


--declare @CurrentI as int
--set @CurrentI = 4
--declare @sql nchar(3000)

      set @sql = '      insert into
                  [user]
                  (username)
            values
      ('''+@prefix +  @CurrentI   +''')'
      
--      print @sql
      exec sp_executesql @sql
      SET @CurrentI = (@CurrentI + 1) --Loop

END --WHILE

COMMIT TRAN
print ' Transaction Committed'
return -1
PROBLEM:
IF (@Rows > 0)
BEGIN
      --DECLARE @CurrentI int
      --SET @CurrentI = 3
      PRINT 'Error occurred at ' + (CAST(@CurrentI as Varchar(4)))
        ROLLBACK TRAN
       return @CurrentI
      PRINT 'Rolled back'
END

      
--delete  from users



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-- exec AddUsersIncremental_sp 'joe' , 2 , 7
/*      
insert into [user]
(username) values
('joe4' )
*/
--  select * from [user]
-- delete from [user]

0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17631589
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question