DECLARE @temp TABLE ( [id] INT IDENTITY(1, 1) , [Name] VARCHAR(10) ) DECLARE @tempId INT , @tempName VARCHAR(10) INSERT INTO @temp VALUES ( 'a' ) INSERT INTO @temp VALUES ( 'b' ) INSERT INTO @temp VALUES ( 'c' ) INSERT INTO @temp VALUES ( 'd' ) INSERT INTO @temp VALUES ( 'e' )
表变量实现表遍历
WHILE EXISTS ( SELECT [id]FROM @temp ) BEGIN SELECT TOP 1 @tempId = [id] , @tempName = [Name] FROM @temp DELETE FROM @tempWHERE [id] = @tempId END
利用游标遍历表
DECLARE @tempId INT , @tempName VARCHAR(10) DECLARE test_Cursor CURSOR LOCAL FOR SELECT [id],[name] FROM @temp
OPEN test_Cursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM test_Cursor INTO @tempId,@tempname PRINT 'Name:----' + @tempName END CLOSE test_Cursor DEALLOCATE test_Cursor