SqlServer全表遍历

 

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