interlect
asked on
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
<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
MikeWalsh
look up BREAK
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
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
ASKER
I tried the rowcount, and no go.
it doesn't do anything at all.
Mickey
it doesn't do anything at all.
Mickey
ASKER
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]
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.