Introduction
The original tip avoided the use of cursors, but in exchange, used table row modifications inside of a transaction. This leads to its own share of problems.
Ideally, traversing the rows in a table would require nothing but table reads. This tip shows one way to accomplish this.
Using the code
I'll use the same table structure as the original tip:
CREATE TABLE [dbo].[A](
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] [int] NOT NULL,
[Flag] [bit] NULL )
In addition to traversing an entire table, this method allows us to specify a contiguous subset of the table to traverse if desired. To begin, set the value
of the @Id
variable to one less than the first item. This is important; we get the next item in the list at the top of the loop, so this ensures that we don't skip the first item.
Secondly, set the value of @LastID
to the id of the last item to be processed.
Code your task after the "--code here" comment, and you're ready to go.
BEGIN TRY
BEGIN TRANSACTION
DECLARE @Id INT, @LastID INT
SELECT @Id = MIN([Id])-1 FROM A
SELECT @LastId = MAX([Id])-1 FROM A
WHILE @Id <= @LastID
BEGIN
SELECT TOP 1 @Id=[Id] FROM A WHERE [Id] > @Id ORDER BY [Id]
PRINT @id
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR ('Error, Please try again.',16,1)
END CATCH
Enjoy!
History
8/3/2012: Original submission.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.