刚写完的一个用户遍历更新的SQL存储过程,分享一下吧

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
 
/*
　填充用户评价等级
*/
ALTER PROC [dbo].[Fill_Userinfos_BuyerRank_SellerRank]
    @userid VARCHAR(12) = NULL
AS 
    BEGIN
        DECLARE @rankCount INT 
        SET @rankCount = 0
        DECLARE cursor1 CURSOR
        FOR
            SELECT  a.UserID
            FROM    C2CEDU.dbo.UserInfos a
                    INNER JOIN dbo.UserBases b ON a.UserID = b.UserID
            WHERE   b.Status <> 400 
        OPEN cursor1
        FETCH NEXT FROM cursor1 INTO @userid
        WHILE @@fetch_status = 0           --判断是否成功获取数据
            BEGIN
                    -- 更新用户作为买家等级　更新卖家等级
                DECLARE @sellerrankvalue INT
                SELECT  @sellerrankvalue = ISNULL(SUM(rankvalue), 0)
                FROM    [C2CEDU].[dbo].[UserRankRecords]
                WHERE   RankType = 1
                        AND ToUserID = @userid
     
                DECLARE @buyerrankvalue INT
                SELECT  @buyerrankvalue = ISNULL(SUM(rankvalue), 0)
                FROM    [C2CEDU].[dbo].[UserRankRecords]
                WHERE   RankType = 0
                        AND ToUserID = @userid
           
                UPDATE  dbo.UserInfos
                SET     BuyerRank = ( SELECT    ISNULL(SUM(rankvalue), 0)
                                      FROM      [C2CEDU].[dbo].[UserRankRecords]
                                      WHERE     RankType = 0
                                                AND ToUserID = @userid
                                    ) ,
                        SellerRank = ( SELECT   ISNULL(SUM(rankvalue), 0)
                                       FROM     [C2CEDU].[dbo].[UserRankRecords]
                                       WHERE    RankType = 1
                                                AND ToUserID = @userid
                                     ) ,
                        BuyerRankInfoID = CASE WHEN @buyerrankvalue >= 0
                                                    AND @buyerrankvalue <= 3
                                               THEN 41  --0级
                                               WHEN @buyerrankvalue > 3
                                                    AND @buyerrankvalue <= 10
                                               THEN 1 --1级
                                               WHEN @buyerrankvalue > 10
                                                    AND @buyerrankvalue <= 40
                                               THEN 3 --2级
                                               WHEN @buyerrankvalue > 40
                                                    AND @buyerrankvalue <= 90
                                               THEN 5 --3级
                                               WHEN @buyerrankvalue > 90
                                                    AND @buyerrankvalue <= 150
                                               THEN 7 --4级
                                               WHEN @buyerrankvalue > 150
                                                    AND @buyerrankvalue <= 250
                                               THEN 9 --5级
                                               WHEN @buyerrankvalue > 250
                                                    AND @buyerrankvalue <= 500
                                               THEN 11--6级
                                               WHEN @buyerrankvalue > 500
                                                    AND @buyerrankvalue <= 1000
                                               THEN 13 --7级
                                               WHEN @buyerrankvalue > 1000
                                                    AND @buyerrankvalue <= 2000
                                               THEN 15 --8级   
                                               WHEN @buyerrankvalue > 2000
                                                    AND @buyerrankvalue <= 5000
                                               THEN 17 --9级
                                               WHEN @buyerrankvalue > 5000
                                                    AND @buyerrankvalue <= 10000
                                               THEN 19--10级
                                               WHEN @buyerrankvalue > 10000
                                                    AND @buyerrankvalue <= 20000
                                               THEN 21 --11级
                                               WHEN @buyerrankvalue > 20000
                                                    AND @buyerrankvalue <= 50000
                                               THEN 23 --12级 
                                               WHEN @buyerrankvalue > 50000
                                                    AND @buyerrankvalue <= 100000
                                               THEN 25 --13级
                                               WHEN @buyerrankvalue > 100000
                                                    AND @buyerrankvalue <= 200000
                                               THEN 27--14级
                                               WHEN @buyerrankvalue > 200000
                                                    AND @buyerrankvalue <= 500000
                                               THEN 29 --15级
                                               WHEN @buyerrankvalue > 500000
                                                    AND @buyerrankvalue <= 1000000
                                               THEN 31 --16级  
                                               WHEN @buyerrankvalue > 1000000
                                                    AND @buyerrankvalue <= 2000000
                                               THEN 33 --17级 
                                               WHEN @buyerrankvalue > 2000000
                                                    AND @buyerrankvalue <= 5000000
                                               THEN 35 --18级
                                               WHEN @buyerrankvalue > 5000000
                                                    AND @buyerrankvalue <= 10000000
                                               THEN 37--19级
                                               WHEN @buyerrankvalue > 10000000
                                                    AND @buyerrankvalue <= 100000000
                                               THEN 39 --20级
                                          END ,
                        SellerRankInfoID = CASE WHEN @sellerrankvalue >= 0
                                                     AND @sellerrankvalue <= 3
                                                THEN 42  --0级
                                                WHEN @sellerrankvalue > 3
                                                     AND @sellerrankvalue <= 10
                                                THEN 2 --1级
                                                WHEN @sellerrankvalue > 10
                                                     AND @sellerrankvalue <= 40
                                                THEN 4 --2级
                                                WHEN @sellerrankvalue > 40
                                                     AND @sellerrankvalue <= 90
                                                THEN 6 --3级
                                                WHEN @sellerrankvalue > 90
                                                     AND @sellerrankvalue <= 150
                                                THEN 8 --4级
                                                WHEN @sellerrankvalue > 150
                                                     AND @sellerrankvalue <= 250
                                                THEN 10 --5级
                                                WHEN @sellerrankvalue > 250
                                                     AND @sellerrankvalue <= 500
                                                THEN 12--6级
                                                WHEN @sellerrankvalue > 500
                                                     AND @sellerrankvalue <= 1000
                                                THEN 14 --7级
                                                WHEN @sellerrankvalue > 1000
                                                     AND @sellerrankvalue <= 2000
                                                THEN 16 --8级   
                                                WHEN @sellerrankvalue > 2000
                                                     AND @sellerrankvalue <= 5000
                                                THEN 18 --9级
                                                WHEN @sellerrankvalue > 5000
                                                     AND @sellerrankvalue <= 10000
                                                THEN 20--10级
                                                WHEN @sellerrankvalue > 10000
                                                     AND @sellerrankvalue <= 20000
                                                THEN 22 --11级
                                                WHEN @sellerrankvalue > 20000
                                                     AND @sellerrankvalue <= 50000
                                                THEN 24 --12级 
                                                WHEN @sellerrankvalue > 50000
                                                     AND @sellerrankvalue <= 100000
                                                THEN 26 --13级
                                                WHEN @sellerrankvalue > 100000
                                                     AND @sellerrankvalue <= 200000
                                                THEN 28--14级
                                                WHEN @sellerrankvalue > 200000
                                                     AND @sellerrankvalue <= 500000
                                                THEN 30 --15级
                                                WHEN @sellerrankvalue > 500000
                                                     AND @sellerrankvalue <= 1000000
                                                THEN 32 --16级  
                                                WHEN @sellerrankvalue > 1000000
                                                     AND @sellerrankvalue <= 2000000
                                                THEN 34 --17级 
                                                WHEN @sellerrankvalue > 2000000
                                                     AND @sellerrankvalue <= 5000000
                                                THEN 36 --18级
                                                WHEN @sellerrankvalue > 5000000
                                                     AND @sellerrankvalue <= 10000000
                                                THEN 38--19级
                                                WHEN @sellerrankvalue > 10000000
                                                     AND @sellerrankvalue <= 100000000
                                                THEN 40 --20级
                                           END
                WHERE   UserID = @userid
            END
                        
 
        FETCH NEXT FROM cursor1 INTO @userid
 
        CLOSE cursor1
        DEALLOCATE cursor1
    END

作者：仓储大叔，张占岭，
荣誉：微软MVP