Traverse a MSSQL table without using CURSOR

Abdul Quader Mamun

5.00/5 (1 vote)

Aug 3, 2012

CPOL

13052

To traverse a table without cursor, you only need to add an additional field in the table with null value. Here is the code for traversing a table.

Table structure here. In my case Flag is the additional field which contains null value.

CREATE TABLE [dbo].[A](
	[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Value] [int] NOT NULL,
	[Flag] [bit] NULL
)

Here is the code for traversing

  
BEGIN TRY	

	BEGIN TRANSACTION

	DECLARE @Id int	
		
	WHILE  EXISTS(SELECT TOP 1 * FROM A WHERE Flag IS null)
		BEGIN	
		     --code here
			
		     SELECT TOP 1 @Id=ID FROM A WHERE Flag IS null	
		     UPDATE A WITH (TABLOCK) SET Flag=1 WHERE Id=@Id
		END

	UPDATE A  WITH (TABLOCK) SET Flag=null	
	COMMIT TRANSACTION	
	
END TRY
BEGIN CATCH

	ROLLBACK TRANSACTION
	RAISERROR ('Error, Please try again.',16,1)
		
END CATCH