November 22, 2004 at 5:01 pm
Hi all,
I am trying to loop through the rows in a temp table, and change the status of these rows from "Active" to "Pending" and log this change in a "modification" table. However, I'm having trouble with the final row, it seems that it is being processed twice! Hence an error is thrown
Server: Msg 2627, Level 14, State 1, Line 31
Violation of PRIMARY KEY constraint 'PK__st_modifications__XXXXXXX'. Cannot insert duplicate key in object 'st_modifications'.
The statement has been terminated.
Server: Msg 2627, Level 14, State 1, Line 37
Violation of PRIMARY KEY constraint 'PK__st_modifications__XXXXXXX'. Cannot insert duplicate key in object 'st_modifications'.
The statement has been terminated.
I think I may have a messy, non-working final TRANSACTION check too. Can someone please help me on this? Thanks in advance.
-------------------------------------------------------
DECLARE @currentRow int, @count int, @error int,
@row int, @pf_cat char(3), @type char(3)
SET @currentRow = 0
SET @count = 0
SET @error = 0
BEGIN TRAN
WHILE @currentRow IS NOT NULL
BEGIN
-- Start from the smallest row_ids in #Pending, loop through the whole talbe
SET @currentRow = (SELECT MIN(row_id) FROM #Pending
WHERE row_id > @currentRow AND row_id <> @currentRow)
SELECT @row = row_id,
@pf_cat = pf_category_hj,
@type = type
FROM #Pending WHERE row_id = @currentRow
IF @pf_cat = '001'
BEGIN
IF @type = 'Inv'
BEGIN
UPDATE com_flat_order_variable
SET col25 = 'Pending', col26 = 'Delinquent Invoice'
WHERE row_id = @row
SET @error = @error + @@ERROR
INSERT st_modifications
VALUES ('235', 'Suspension Status', getdate(), 'COMPLETE',
@row, getdate(), 'Active', 'Pending', 'silkadmin')
SET @error = @error + @@ERROR
INSERT st_modifications
VALUES ('235', 'Suspension Reason', getdate(), 'COMPLETE',
@row, getdate(), '', 'Delinquent Invoice', 'silkadmin')
SET @error = @error + @@ERROR
IF @error > 0
ROLLBACK TRAN
END
ELSE IF @type = 'CC'
BEGIN
UPDATE com_flat_order_variable
SET col25 = 'Pending', col26 = 'Failed Credit Card'
WHERE row_id = @row
SET @error = @error + @@ERROR
INSERT st_modifications
VALUES ('235', 'Suspension Status', getdate(), 'COMPLETE',
@row, getdate(), 'Active', 'Pending', 'silkadmin')
SET @error = @error + @@ERROR
INSERT st_modifications
VALUES ('235', 'Suspension Reason', getdate(), 'COMPLETE',
@row, getdate(), '', 'Failed Credit Card', 'silkadmin')
SET @error = @error + @@ERROR
IF @error > 0
ROLLBACK TRAN
END
END
-- Cut out some "ELSE IF @pf_cat = '002'...'0XX'" to simplify viewing
PRINT 'This is the ' + cast(@count as char(9)) + ' iteration. ' + cast(@row as varchar(12)) + ' ' + @pf_cat + ' ' + @type + '.'
SET @count = @count + 1
END
IF @@ROWCOUNT = 0
BEGIN
PRINT 'Nothing in #Pending table.'
RETURN
END
ELSE IF @error = 0
COMMIT TRAN
November 22, 2004 at 5:56 pm
Instead of
WHILE @currentRow IS NOT NULL
Try
WHILE EXSITS (SELECT * FROM #Pending WHERE row_id > @currentRow)
Currently, let's say you're on the last record. The statement
SET @currentRow = (SELECT MIN(row_id) FROM #Pending
WHERE row_id > @currentRow AND row_id @currentRow)
sets @currentRow = LAST_ID and the row gets processed. The WHILE loop will run an additional time, however, since @currentRow is not NULL, yet. On the final run through, @currentRow gets set to NULL, but all of the other statements get run, as well, with the previous values for all of the other variables.
November 23, 2004 at 4:45 am
Hi,
Lam is right, you need to set @CurrentRow to its initial value immediately prior to the WHILE loop and then set it's next value as the last thing you do in the loop.
e.g.
SET @CurrentRow = (To the first value required)
WHILE @CurrentRow IS NOT NULL
BEGIN
-- DO ALL PROCESSING --
SET @CurrentRow = (Next value)
END
If its set as the first item in the WHILE loop everything afterwards will be processed even when it gets set to NULL. Set as the last item in the loop, it gets evaluated before anything else is processed.
Have fun
Steve
We need men who can dream of things that never were.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply