代替游标的循环
2011-03-02 14:24 通心菜 阅读(248) 评论(0) 编辑 收藏 举报
Declare @Loop int --循环变量
DECLARE @RowCnt INT --总记录
DECLARE @UserID INT
DECLARE @PubSites VARCHAR(8000)
Set @Loop = 1
SELECT @RowCnt=COUNT(*) FROM dbo.UserConfig
While @Loop < @RowCnt
Begin
--获取站点列表
SELECT @UserID=UserID,@PubSites=PubSites FROM(SELECT UserID,PubSites,ROW_NUMBER() OVER(ORDER BY ConfigID) AS RowNo FROM dbo.UserConfig)AS t WHERE RowNo=@Loop
UPDATE dbo.UserSite SET ActiveFlag=1 WHERE userid=@UserID AND SiteID IN(select F1 from split(@PubSites,','))
set @Loop=@Loop+1
PRINT CONVERT(varchar(10),@Loop)+'/'+CONVERT(varchar(10),@RowCnt)
End
DECLARE @RowCnt INT --总记录
DECLARE @UserID INT
DECLARE @PubSites VARCHAR(8000)
Set @Loop = 1
SELECT @RowCnt=COUNT(*) FROM dbo.UserConfig
While @Loop < @RowCnt
Begin
--获取站点列表
SELECT @UserID=UserID,@PubSites=PubSites FROM(SELECT UserID,PubSites,ROW_NUMBER() OVER(ORDER BY ConfigID) AS RowNo FROM dbo.UserConfig)AS t WHERE RowNo=@Loop
UPDATE dbo.UserSite SET ActiveFlag=1 WHERE userid=@UserID AND SiteID IN(select F1 from split(@PubSites,','))
set @Loop=@Loop+1
PRINT CONVERT(varchar(10),@Loop)+'/'+CONVERT(varchar(10),@RowCnt)
End
