CODEWITHSUNDEEP
sql-servercursor
Matthew Cox
Matthew Cox

Reputation: 13672

Cursor stuck in an infinite loop

First attempt at a cursor so take it easy =P The cursor is supposed to grab a list of company ids that are all under a umbrella group. Then target a specific company and copy its workflow records to the companies in the cursor.

It infinitely inserts these workflow records into all the companies ... what is the issue here?

Where is the n00b mistake?

DECLARE @GroupId int = 36;
DECLARE @CompanyToCopy int = 190
DECLARE @NextId int;
Declare @Companies CURSOR;

SET @Companies = CURSOR FOR 
SELECT CompanyId
FROM Company C 
    INNER JOIN [Group] G 
        ON C.GroupID = G.GroupID
WHERE   C.CompanyID != 190 
        AND
        G.GroupId = @GroupId
        AND
        C.CompanyID != 0

OPEN @Companies
FETCH NEXT
FROM @Companies INTO @NextId

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO COI.Workflow(CompanyID, EndOfWorkflowAction, LetterType, Name)
    (SELECT 
            @NextId,
            W.EndOfWorkflowAction,
            W.LetterType,
            W.Name
     FROM COI.Workflow W)

    FETCH NEXT
    FROM @Companies INTO @NextId
END
CLOSE @Companies;
DEALLOCATE @Companies;

Edit:

I decided to attempt making this set based just because after being told to do it ... I realized I didn't really quite have the answer as to how to do it as a set based query.

Thanks for all the help everyone. I'll post the set based version for posterity.

INSERT INTO COI.Workflow(CompanyID, EndOfWorkflowAction, LetterType, Name)
(
SELECT 
    CG.CompanyId,
    W.EndOfWorkflowAction,
    W.LetterType,
    W.Name
FROM COI.Workflow W
     CROSS JOIN (SELECT C.CompanyID
                 FROM Company C 
                    INNER JOIN [Group] G
                        ON G.GroupID = C.GroupID
                 WHERE  C.CompanyID != 190 
                        AND 
                        C.CompanyID != 0
                        AND 
                        G.GroupID = 36
                 ) AS CG
 WHERE W.CompanyID = 190
 )

Upvotes: 0

Views: 2850

Answers (4)

Alex_L
Alex_L

Reputation: 2666

First use of cursor is OK, all problems in INSERT ... SELECT logic. I cannot understand what do you need to insert into COI.Workflow table. I agree with previous commentatorts that your current WHERE condition doubles records, but I cannot believe that you want to insert the full-doubled records for each company each time. so, I think you need something like 

INSERT INTO COI.Workflow(CompanyID, EndOfWorkflowAction, LetterType, Name)
(SELECT TOP 1
        @NextId,
        W.EndOfWorkflowAction,
        W.LetterType,
        W.Name
 FROM COI.Workflow W)

Or, we need to know more about your logic of inserting the records.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300529

I beieve your logic is wrong (it's somewhat hidden because of the use of a cursor!).

Your posted code is attempting to insert a row into into COI.Workflow for every row in COI.Workflow times the number of companies matching your first select's conditions. (Notice how your insert's SELECT statement has no condition: you are selecting the whole table). On each time through the loop, you are doubling the number of rows in COI.Workflow

So, it's not infinite but it could well be very, very long!

I suggest you rewrite as a set based statement and the logic will become clearer.

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89661

You have no WHERE condition on this:

SELECT 
            @NextId,
            W.EndOfWorkflowAction,
            W.LetterType,
            W.Name
     FROM COI.Workflow W
     -- WHERE CompanyID = @CompanyToCopy -- This should be here

So you are getting a kind of doubling effect.

initial state, company 190, seed row (0)

pass one, company 2, copy of seed row (1)
now 2 rows

pass two, company 3, copy of seed row (0) - call this (2)
pass two, company 3, copy of copy of seed row (1) - call this (3)
now 4 rows

then 8 rows, etc

Upvotes: 3

Guffa
Guffa

Reputation: 700232

You are inserting a new copy of all workflow records in the workflow table for each iteration, so it will double in size each time. If you for example have 30 items in your cursor, you will end up with a workflow table with 1073741824 times more records than it had before.

Upvotes: 2

Related Questions