We help IT Professionals succeed at work.

Transaction rollback with WHILE

interlect
interlect asked
on
759 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
Comment
Watch Question

look up BREAK
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Author

Commented:
I tried the rowcount, and no go.
it doesn't do anything at all.

Mickey

Author

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]

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.