存储过程帮助 [英] stored procedure help

问题描述

我继承了以下存储过程。它做了它打算做的事情，但现在需要清理（可能会删除游标）。有什么指针吗？我知道这是一团糟。我为帖子的长度道歉，但它是整个sp。

I have inherited the following stored procedure. It does what it "was" intended to do but now needs to be cleaned up (maybe do away with cursors). Any pointers? I know it is a mess. I apologize for the length of the post but it is the entire sp.

ALTER PROCEDURE [dbo].[sp_AssignFellowBox]
    @competitionid int,
    @Num_cts int
AS
BEGIN
    SET NOCOUNT ON;

    -- Common variable declarations
    DECLARE @Index INT
    DECLARE @Num_checks INT
    DECLARE @Num_box INT
    DECLARE @Rev_ID nvarchar(255), @Nom_ID varchar(8)
    DECLARE @RevsPerReviewer INT
    DECLARE @Row_test INT
    DECLARE @Left_overs INT     
    DECLARE @RevCursorStatus INT    
    DECLARE @NomCursorStatus INT
declare @compNum int
create table #RedoChecks (Nom_ID varchar(8))        
set @compNum = (select comp_to_review_id from Competition where id=@competitionid)

select "Application Number" into #checks_uf
from Twen_Table
where UF='Y'
and comp_id = @compNum
and nominated=1
SET @Num_checks = @@rowcount
    select * into #mult_checks_uf
from #checks_uf order by newid()

SET @Index = 1 ;    -- reinit variable
WHILE @Index < @Num_cts BEGIN
    insert into #mult_checks_uf
    select * from #checks_uf order by newid()
    SET @Index = @Index + 1
END
-- Create temp table for Box
select uf_rev_id into #temp_box_uf
from Reviewer_Competition
where uf_rev_id like 'UF%'
and competition_id = @compNum
--and Term_Exp >= '2009'
SET @Num_box = @@rowcount
SET @RevsPerReviewer = (@Num_checks * @Num_cts) / nullif(@Num_box,0)    -- Declare and open cursors
DECLARE @ReviewerCursorUF CURSOR
SET @ReviewerCursorUF = CURSOR FAST_FORWARD FOR
    select * from #temp_box_uf order by newid()
OPEN @ReviewerCursorUF
DECLARE @CheckCursorUF CURSOR
SET @CheckCursorUF = CURSOR FAST_FORWARD FOR
    select * from #mult_checks_uf
OPEN @CheckCursorUF

-- Begin fetching
FETCH NEXT FROM @ReviewerCursorUF into @Rev_ID
SET @RevCursorStatus = @@FETCH_STATUS
WHILE @RevCursorStatus = 0
BEGIN
    SET @Index = 0

            SET @Row_test = (SELECT count(Nom_ID) FROM #RedoChecks)
    IF (@Row_Test > 0)
    BEGIN

        INSERT INTO Summit_Table (Rev_ID, Nom_ID, Score, comp_id)
        SELECT @Rev_ID, Nom_ID, 0, @compNum FROM #RedoChecks

                    DELETE FROM #RedoChecks
        WHERE Nom_ID is not null


        SET @Index = @Row_test
    END

        WHILE @Index < @RevsPerReviewer
    BEGIN
        FETCH NEXT FROM @CheckCursorUF into @Nom_ID
        SET @NomCursorStatus = @@FETCH_STATUS
        IF @NomCursorStatus = 0
        BEGIN
            --Check if already in Summit_Table table
            SET @Row_test = (SELECT count(Nom_ID) FROM Summit_Table
                                WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @compNum))

                            IF (@Row_test > 0)
            BEGIN
                --insert into temp table
                INSERT INTO #RedoChecks VALUES (@Nom_ID)
                SET @Index = @Index - 1                 END
            ELSE
            BEGIN
                INSERT INTO Summit_Table VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @compNum)
            END
        END
        SET @Index = @Index + 1
    END
    FETCH NEXT FROM @ReviewerCursorUF into @Rev_ID
    SET @RevCursorStatus = @@FETCH_STATUS
END
-- Clean up time
CLOSE @ReviewerCursorUF
DEALLOCATE @ReviewerCursorUF
CLOSE @CheckCursorUF
DEALLOCATE @CheckCursorUF

-- Find which checks and randomly assign
DECLARE @CheckLeftoversUF CURSOR
SET @CheckLeftoversUF = CURSOR FAST_FORWARD FOR
    SELECT Nom_ID from Summit_Table
    where comp_id = @compNum
    GROUP BY Nom_ID
    HAVING COUNT(Nom_ID) < @Num_cts
OPEN @CheckLeftoversUF

DECLARE @ReviewerExtraUF CURSOR
SET @ReviewerExtraUF = CURSOR FAST_FORWARD FOR
    SELECT Rev_ID FROM Summit_Table
    WHERE Rev_ID like 'UF%'
    and comp_id = @compNum
    GROUP BY Rev_ID
    HAVING count(Rev_ID) = @RevsPerReviewer --Find only box without any appointed overage reviews
OPEN @ReviewerExtraUF

DECLARE @num_leftovers INT
SET @num_leftovers = (SELECT COUNT(A.Nom_ID) FROM 
                        (SELECT COUNT(Nom_ID) AS Nom_ID FROM Summit_Table
                        GROUP BY Nom_ID
                        HAVING COUNT(Nom_ID) < @Num_cts) AS A
                        GROUP BY Nom_ID)
WHILE (@num_leftovers > 0)
BEGIN
    --Each itteration, re-open cursor to only be selecting checks that need another review
    CLOSE @CheckLeftoversUF
    SET @CheckLeftoversUF = CURSOR FAST_FORWARD FOR
        SELECT Nom_ID from Summit_Table
        GROUP BY Nom_ID
        HAVING COUNT(Nom_ID) < @Num_cts
    OPEN @CheckLeftoversUF
    FETCH NEXT FROM @CheckLeftoversUF into @Nom_ID
    SET @NomCursorStatus = @@FETCH_STATUS
    WHILE (@NomCursorStatus = 0)
    BEGIN
        FETCH NEXT FROM @ReviewerExtraUF into @Rev_ID
        SET @RevCursorStatus = @@FETCH_STATUS
        WHILE (@RevCursorStatus = 0)
        BEGIN
            --Check if currently in the table
            SET @Row_test = (SELECT count(Nom_ID) FROM Summit_Table
                                WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @compNum))

            --if not, add to table
            IF (@Row_test = 0)
            BEGIN
                --Insert into table
                INSERT INTO Summit_Table VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @compNum)
                set @num_leftovers = @num_leftovers - 1
                set @NomCursorStatus = -1
                set @RevCursorStatus = -1

                --Reopen Cursor; only grab list of box with no extra checks
                CLOSE @ReviewerExtraUF
                SET @ReviewerExtraUF = CURSOR FAST_FORWARD FOR
                    SELECT Rev_ID FROM Summit_Table
                    WHERE Rev_ID like 'UF%'
                    and comp_id = @compNum
                    GROUP BY Rev_ID
                    HAVING count(Rev_ID) = @RevsPerReviewer --Find only box without any appointed overage reviews
                OPEN @ReviewerExtraUF
            END
            --if so, fetch next reviewer; not Check
            ELSE
            BEGIN
                FETCH NEXT FROM @ReviewerExtraUF into @Rev_ID
            END
        END
        FETCH NEXT FROM @CheckLeftoversUF into @Nom_ID
    END
END

CLOSE @ReviewerExtraUF
DEALLOCATE @ReviewerExtraUF
CLOSE @CheckLeftoversUF
DEALLOCATE @CheckLeftoversUF

-- * * * * * Part 2: Pair GE Checks with GE Box * * * * * --
select "Application Number" into #checks_ge
from Twen_Table
where GE='Y'
and comp_id = @compNum
SET @Num_checks = @@rowcount

-- Insert first randomized list of checks
select * into #mult_checks_ge
from #checks_ge order by newid()

-- Insert (Reviews per Check - 1) more randomized lists
SET @Index = 1 ;    -- reinit variable
WHILE @Index < @Num_cts BEGIN
    insert into #mult_checks_ge
    select * from #checks_ge order by newid()
    SET @Index = @Index + 1
END

-- Create temp table for Box
select uf_rev_id into #temp_box_ge
from Reviewer_Comp
where uf_rev_id like 'GE%'
and competition_id = @compNum
--and Term_Exp >= '2009'
SET @Num_box = @@rowcount
SET @RevsPerReviewer = (@Num_checks * @Num_cts) / nullif(@Num_box,0)

-- Declare and open cursors
DECLARE @ReviewerCursorGE CURSOR
SET @ReviewerCursorGE = CURSOR FAST_FORWARD FOR
    select * from #temp_box_ge order by newid()
OPEN @ReviewerCursorGE

DECLARE @CheckCursorGE CURSOR
SET @CheckCursorGE = CURSOR FAST_FORWARD FOR
    select * from #mult_checks_ge
OPEN @CheckCursorGE

-- Begin fetching
FETCH NEXT FROM @ReviewerCursorGE into @Rev_ID
SET @RevCursorStatus = @@FETCH_STATUS
WHILE @RevCursorStatus = 0
BEGIN
    SET @Index = 0

    --First, check extras table from previous reviewer; if not empty, fill these checks first
    SET @Row_test = (SELECT count(Nom_ID) FROM #RedoChecks)
    IF (@Row_Test > 0)
    BEGIN
        --insert all previous checks to Summit_Table first
        INSERT INTO Summit_Table (Rev_ID, Nom_ID, Score, comp_id)
        SELECT @Rev_ID, Nom_ID, 0, @compNum FROM #RedoChecks

        --remove from extras table so they are not doubly added
        DELETE FROM #RedoChecks
        WHERE Nom_ID is not null

        --update @Index to reflect checks already added
        SET @Index = @Row_test
    END

    --Start adding GE checks
    WHILE @Index < @RevsPerReviewer
    BEGIN
        FETCH NEXT FROM @CheckCursorGE into @Nom_ID
        SET @NomCursorStatus = @@FETCH_STATUS
        IF @NomCursorStatus = 0
        BEGIN
            --Check if already in Summit_Table table
            SET @Row_test = (SELECT count(Nom_ID) FROM Summit_Table
                                WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @compNum))

            --if so, add Check to extras table and decrement @Index
            IF (@Row_test > 0)
            BEGIN
                --insert into temp table
                INSERT INTO #RedoChecks VALUES (@Nom_ID)
                SET @Index = @Index - 1 -- Decrement so that the current reviewer still gets @RevsPerReviewer checks to review
            END
            ELSE
            BEGIN
                INSERT INTO Summit_Table VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @compNum)
            END
        END
        SET @Index = @Index + 1
    END
    FETCH NEXT FROM @ReviewerCursorGE into @Rev_ID
    SET @RevCursorStatus = @@FETCH_STATUS
END
-- Clean up time
CLOSE @ReviewerCursorGE
DEALLOCATE @ReviewerCursorGE
CLOSE @CheckCursorGE
DEALLOCATE @CheckCursorGE

-- At this point, all box have equal # of reviews, but some nominess only in there twice...
-- Find which checks and randomly assign to Reviewer
DECLARE @CheckLeftoversGE CURSOR
SET @CheckLeftoversGE = CURSOR FAST_FORWARD FOR
    SELECT Nom_ID from Summit_Table
    where comp_id = @compNum
    GROUP BY Nom_ID
    HAVING (COUNT(Nom_ID) % @Num_cts <> 0) -- cannot assume COUNT(Nom_ID) < @Num_cts b/c nominess with both UF and GE
    --HAVING (COUNT(Nom_ID) < @Num_cts) or (COUNT(Nom_ID) = 4) or (COUNT(Nom_ID) = 5) -- 4,5 for current quarter; should really be "HAVING COUNT(Nom_ID) MOD @Num_cts <> 0" .... I think
OPEN @CheckLeftoversGE

DECLARE @ReviewerExtraGE CURSOR
SET @ReviewerExtraGE = CURSOR FAST_FORWARD FOR
    SELECT DISTINCT Rev_ID FROM Summit_Table
    WHERE Rev_ID like 'GE%'
    and comp_id = @compNum
OPEN @ReviewerExtraGE

SET @num_leftovers = (select count(*) from (SELECT count(Nom_ID) AS Nom_ID FROM Summit_Table
                        GROUP BY Nom_ID
                        HAVING (COUNT(Nom_ID) % @Num_cts <> 0)) as B) -- cannot assume COUNT(Nom_ID) < @Num_cts b/c nominess with both UF and GE
                        --HAVING (COUNT(Nom_ID) < 3) or (COUNT(Nom_ID) = 4) or (COUNT(Nom_ID) = 5)) as B) -- once again, should really be "HAVING COUNT(Nom_ID) MOD @Num_cts <> 0"
WHILE (@num_leftovers > 0)
BEGIN
    --Each itteration, re-open cursor to only be selecting checks that need another review
    CLOSE @CheckLeftoversGE
    SET @CheckLeftoversGE = CURSOR FAST_FORWARD FOR
        SELECT Nom_ID from Summit_Table
        where comp_id = @compNum
        GROUP BY Nom_ID
        HAVING (COUNT(Nom_ID) < @Num_cts) or (COUNT(Nom_ID) = 4) or (COUNT(Nom_ID) = 5)     OPEN @CheckLeftoversGE                              FETCH NEXT FROM @CheckLeftoversGE into @Nom_ID
    SET @NomCursorStatus = @@FETCH_STATUS
    WHILE (@NomCursorStatus = 0)
    BEGIN
        FETCH NEXT FROM @ReviewerExtraGE into @Rev_ID
        SET @RevCursorStatus = @@FETCH_STATUS
        WHILE (@RevCursorStatus = 0)
        BEGIN
            --Check if currently in the table
            SET @Row_test = (SELECT count(Nom_ID) FROM Summit_Table
                                WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @compNum))

            --if not, add to table
            IF (@Row_test = 0)
            BEGIN
                --Insert into table
                INSERT INTO Summit_Table VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @compNum)
                set @num_leftovers = @num_leftovers - 1
                set @NomCursorStatus = -1
                set @RevCursorStatus = -1

                --Reopen Cursor; only grab list of box with no extra checks
                CLOSE @ReviewerExtraGE
                SET @ReviewerExtraGE = CURSOR FAST_FORWARD FOR
                    SELECT Rev_ID FROM Summit_Table
                    WHERE Rev_ID like 'GE%' and comp_id = @compNum
                    GROUP BY Rev_ID
                    HAVING count(Rev_ID) = @RevsPerReviewer --Find only box without any appointed overage reviews
                OPEN @ReviewerExtraGE
            END
            --if so, fetch next reviewer; not Check...
            ELSE
            BEGIN
                FETCH NEXT FROM @ReviewerExtraGE into @Rev_ID
            END
        END
        FETCH NEXT FROM @CheckLeftoversGE into @Nom_ID
    END
END

CLOSE @ReviewerExtraGE
DEALLOCATE @ReviewerExtraGE
CLOSE @CheckLeftoversGE
DEALLOCATE @CheckLeftoversGE


drop table #checks_uf
drop table #mult_checks_uf
drop table #temp_box_uf

drop table #checks_ge
drop table #mult_checks_ge
drop table #temp_box_ge

--select * from #RedoChecks
drop table #RedoChecks 

推荐答案

Without knowing what it is supposed to do and without any sample data or expected results, you are not going to find anyone who is going to work their way through this except you.



If it was me in your position I would be running the queries in Query Analyser / SSMS and examining the data in the temporary tables to see if I could address the query more efficiently / in a clearer way.



For example: Why does #mult_checks_uf contain multiple copies of the [Application Number] values? Use a WHILE loop instead (perhaps).



I also suspect you can remove some of those other cursors and use joins instead.



As I said, a small set of sample data, some expected results and an explanation of what the SP is supposed to achieve would be more likely to garner a useful answer

Without knowing what it is supposed to do and without any sample data or expected results, you are not going to find anyone who is going to work their way through this except you.

If it was me in your position I would be running the queries in Query Analyser / SSMS and examining the data in the temporary tables to see if I could address the query more efficiently / in a clearer way.

For example: Why does #mult_checks_uf contain multiple copies of the [Application Number] values? Use a WHILE loop instead (perhaps).

I also suspect you can remove some of those other cursors and use joins instead.

As I said, a small set of sample data, some expected results and an explanation of what the SP is supposed to achieve would be more likely to garner a useful answer

这篇关于存储过程帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！