Sql Server 2005 遍历结果集方法之一 （已验证通过）
--创建表USE
GO
/****** 对象:Table . 脚本日期: 12/09/2012 17:14:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE .(
IDENTITY(1,1) NOT NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
NULL CONSTRAINT DEFAULT (getdate()),
NULL CONSTRAINT DEFAULT ((0)),
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)WITH (IGNORE_DUP_KEY = OFF) ON
) ON
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 /*定义订单的表变量 用于遍历订单号*/
(
UserNamevarchar(50),
Status int,
UserId int,
flagID TINYINT/*这个表变量中增加一个FLAGID进行数据初始值为0的存放，然后去循环这个记录集，每循环一次，就把对应的FLAGID 的值改成1，然后再根据循环来查找满足条件等于0的情况，每循环一次，处理的记录集就会少一次，达到快速遍历，此种方式比游标 遍历效率高很多*/
)
declare@totalcount int
declare@rownum int
insertinto @UserNamerecordselectUserName,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
页:
[1]