Solved

Transaction rollback with WHILE

Posted on 2006-06-14
6
717 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
Comment Utility
look up BREAK
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
I tried the rowcount, and no go.
it doesn't do anything at all.

Mickey
0
 

Author Comment

by:interlect
Comment Utility
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
Comment Utility
PAQed with points refunded (500)

CetusMOD
Community Support Moderator
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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 setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now