Transaction rollback with WHILE

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
interlectAsked:
Who is Participating?
 
CetusMODConnect With a Mentor Commented:
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0
 
MikeWalshCommented:
look up BREAK
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
interlectAuthor Commented:
I tried the rowcount, and no go.
it doesn't do anything at all.

Mickey
0
 
interlectAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.