刚写完的一个用户遍历更新的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