Sql Server 2005 遍历结果集方法之一

//code by:博客园-曹永思-八字格局分析  

--创建表
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 

.net重写URL：http://www.cnblogs.com/yonsy/archive/2012/09/21/2696935.html

.net URL重写例子

 欢迎转载，转载请注明出处，希望帮到更多人。