Sql Server 2005 遍历结果集方法之一 （已验证通过）

--创建表
USE [dbtest]
GO
/****** 对象: Table [dbo].[tb_Users] 脚本日期: 12/09/2012 17:14:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_Users](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Password] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[AddTime] [datetime] NULL CONSTRAINT [DF_tb_Users_AddTime] DEFAULT (getdate()),
[Status] [int] NULL CONSTRAINT [DF_tb_Users_Status] DEFAULT ((0)),
CONSTRAINT [PK_tb_Users] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Users', @level2type=N'COLUMN', @level2name=N'UserId'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Users', @level2type=N'COLUMN', @level2name=N'UserName'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户密码' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Users', @level2type=N'COLUMN', @level2name=N'Password'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tb_Users', @level2type=N'COLUMN', @level2name=N'AddTime'


--创建表完成

alter PROCEDURE CheckUser
As
Begin
declare @UserNamerecord table /*定义订单的表变量 用于遍历订单号*/
(
UserName varchar(50),
Status int,
UserId int,
flagID TINYINT/*这个表变量中增加一个FLAGID进行数据初始值为0的存放，然后去循环这个记录集，每循环一次，就把对应的FLAGID 的值改成1，然后再根据循环来查找满足条件等于0的情况，每循环一次，处理的记录集就会少一次，达到快速遍历，此种方式比游标 遍历效率高很多*/
)
declare @totalcount int
declare @rownum int
insert into @UserNamerecord select UserName,Status,UserId,0 from tb_Users


/*设置循环总数*/
set @totalcount = @@ROWCOUNT
/*设置循环起始值*/
set @rownum = 1
/* 循环*/
while @rownum <=@totalcount
begin
declare @UserName varchar(50)
declare @Status int
declare @UserId int
select top 1 @UserName=UserName,@UserId=UserId from @UserNamerecord where flagID=0
--你要完成的逻辑
update tb_Users set Status=Status+1 where UserId=@UserId
update @UserNamerecord set flagID=1 where UserId=@UserId--标记为已处理
set @rownum = @rownum + 1
print @UserName
End
End
-------=======================
--执行存储过程
exec CheckUser

select UserId, UserName,Status from tb_Users