SQL Update in While Loop

Question

I'm attempting to send in a number and identifiers and update rows in a while loop.

For instance if @Number = 1

and

MAX(Number) 
FROM QuestionnaireQuestions 
WHERE Questionnaire_ID = @Questionnaire_ID = 4

The results are values 1, 1, 1 when I would expect 3,2,1.

CREATE PROCEDURE [dbo].[DeleteQuestion] 
    (@QuestionnaireQuestions_ID BIGINT,
     @Questionnaire_ID          BIGINT,
     @Number                    SMALLINT)
AS
    DECLARE @i AS SMALLINT

    SELECT @i = MAX(Number)
    FROM   QuestionnaireQuestions
    WHERE  Questionnaire_ID = @Questionnaire_ID

    WHILE ( @i > @Number )
    BEGIN
          UPDATE QuestionnaireQuestions
          SET    Number = ( @i - 1 )
          WHERE  Number = @i
                 AND Questionnaire_ID = @Questionnaire_ID

          SET @i = @i - 1
   END

   DELETE QuestionnaireQuestions
   WHERE  QuestionnaireQuestions_ID = @QuestionnaireQuestions_ID

JBrooks · Accepted Answer · 2015-02-08 21:11:22Z

3

I would rework it to only take a single id. That way you can never pass it values that don't belong together.

CREATE PROCEDURE [dbo].[DeleteQuestion] (@QuestionnaireQuestions_ID BIGINT)
AS
    DECLARE @Questionnaire_ID          BIGINT,
            @Number                    SMALLINT

    SELECT @Questionnaire_ID = Questionnaire_ID,
           @Number = Number
    FROM QuestionnaireQuestions
    WHERE QuestionnaireQuestions_ID = @QuestionnaireQuestions_ID       

    DELETE QuestionnaireQuestions
    WHERE  QuestionnaireQuestions_ID = @QuestionnaireQuestions_ID 

    UPDATE QuestionnaireQuestions
    SET    Number = Number - 1
    WHERE  Questionnaire_ID = @Questionnaire_ID
           AND  Number > @Number

END

edited Feb 8, 2015 at 21:11

answered Feb 8, 2015 at 20:56

JBrooks

10k2 gold badges31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Holly Rasmussen Over a year ago

Your answer is much cleaner however, it still makes zero sense to me why every prossible combo of numbers work with my while loop except 1. Scratching my head...

Holly Rasmussen Over a year ago

I just noticed your edit, indeed, much better to have the single parameter identifier. very good.

Pekka · Accepted Answer · 2015-02-08 20:53:24Z

1

If the question number is not really more than an ordering in the questionnaire, why do you not simply leave the number initially assigned to the question, and then renumber them when they are shown, or otherwise processed.

This way, changing data only has local impact to the affected row.

answered Feb 8, 2015 at 20:53

Pekka

3,67431 silver badges47 bronze badges

2 Comments

Holly Rasmussen Over a year ago

Are you suggesting using the row identifier and if there are gaps then, so be it, we still have an order? That would definitely work but in my grid there are also up and down arrows along with the option to delete so I @Number (which is a sort number really). I do appreciate you taking the time to look at my issue.

Pekka Over a year ago

Correct. The ordering remains even if the sequence is not contiguous. The answer you accepted will work as long as you prevent multiple users from deleting rows in the same questionnaire at the same time. Try to keep the data model separate from your visual layout.

NoChance · Accepted Answer · 2015-02-08 21:05:40Z

0

Below is what I think is happening, assuming ID is not a unique ID...Say you have rows with value 4,3,2,1.

When you first run you select, you get row with 4. When you update it, the table will have the rows: 3,3,2,1.

When you select again, you get row with 3, and when you update you get: 2,3,2,1. Repeating this (R means select result, U means update result):

R: 3

U: 2,2,2,1

R: 2

U: 1,2,2,1

R: 2

U: 1,1,2,1

R: 1

U: 1,1,1,1

One way to solve this is to separate your search criteria to not depend on the updated column.

edited Feb 8, 2015 at 21:05

answered Feb 8, 2015 at 20:49

NoChance

5,7844 gold badges38 silver badges51 bronze badges

Collectives™ on Stack Overflow

SQL Update in While Loop

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related