代码改变世界

代替游标的循环

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