Selecting the values from a table in sql using a loop

Question

I have a table with a certain amount of ids and I want to use these seperate ids to retrieve data from another table.

set @CurrentRow = 0
set @RowsToProcess = (SELECT COUNT(*) FROM @QuestionsPrimaryTable)

WHILE(@CurrentRow < @RowsToProcess)
BEGIN
    DECLARE @id int
    DECLARE @value varchar(200)
    SET @CurrentRow = @CurrentRow + 1

    SELECT @id =  Q.QuestionsId FROM @QuestionsPrimaryTable Q
    SET @value = (SELECT Q.QuestionPrimaryDescription FROM QuestionPrimary Q WHERE Q.QuestionPrimaryID = @id)
    PRINT @value

END

the seperate id values I am trying to retrieve is 5, 7, 9 as it is at the moment I only retrieve value of 9

How can I retrieve the separate id values?

As Tanner said below looping is a last resort. The reason you are getting the values for one id is because you are setting the scalar value of @id with a query that has no where predicate. That means the value retrieved will be the last one in the resultset. In other words, each trip through your loop you keep setting that value to the same thing. Avoid the looping in the first place and your code will be must faster, cleaner, maintainable and this issue will go away. — Sean Lange
– Sean Lange, Commented Feb 20, 2015 at 14:38
Excellent, thanks for the advice...this makes perfect sense, thanks — Arianule
– Arianule, Commented Feb 20, 2015 at 14:40

Tanner · Accepted Answer · 2015-02-20 14:33:27Z

2

I'm not sure if what you're after actually requires a loop. Cursors are very rarely required in SQL, so I'd always look to achieve the result without one if possible.

Are you looking for something like this, where you can JOIN QuestionPrimary and @QuestionsPrimaryTable, and filter results where the ID in 5 or 7 or 9?

SELECT qp.QuestionPrimaryID, qp.QuestionPrimaryDescription 
FROM QuestionPrimary qp
INNER JOIN @QuestionsPrimaryTable qpt 
        ON qp.QuestionPrimaryID = qpt.[JOIN_COLUMN]
WHERE qpt.QuestionPrimaryID IN(5,7,9)

answered Feb 20, 2015 at 14:33

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

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

Comments

animateme · Accepted Answer · 2015-02-20 14:42:21Z

If you absolutely need to loop through this data, you will need to add something to your script that will move to the next record in @QuestionsPrimaryTable. The way it is currently written it sets @Id to the same value during every iteration.

Depending on how you plan to use @QuestionsPrimaryTable, you could simply add a delete into the loop to remove the last record you selected.

set @CurrentRow = 0
set @RowsToProcess = (SELECT COUNT(*) FROM @QuestionsPrimaryTable)

WHILE(@CurrentRow < @RowsToProcess)
BEGIN
    DECLARE @id int
    DECLARE @value varchar(200)
    SET @CurrentRow = @CurrentRow + 1

    SELECT @id =  MAX(Q.QuestionsId) FROM @QuestionsPrimaryTable Q
    SET @value = (SELECT Q.QuestionPrimaryDescription FROM QuestionPrimary Q WHERE Q.QuestionPrimaryID = @id)
    PRINT @value

    DELETE @QuestionsPrimaryTable
    WHERE QuestionsId = @id
END

That being said, there is likely a much better way to accomplish this. If you can elaborate on your question, we can probably provide a better solution for you.

Giorgos Betsos · Accepted Answer · 2015-02-20 15:00:17Z

1

Loops should be avoided wherever a set based approach is feasible. Having said that, in case you definitely want a loop, then this should fix your issue:

SET @CurrentRow = @CurrentRow + 1 -- first value is 1

SELECT @id = Q.QuestionsId 
FROM (
   SELECT QuestionsId, 
          ROW_NUMBER() OVER (ORDER BY QuestionsId) AS rn
   FROM @QuestionsPrimaryTable) Q
WHERE Q.rn =  @CurrentRow

In your code the same QuestionsId value is being fetched for each loop iteration. Using ROW_NUMBER() you can access the @CurrentRow record.

edited Feb 20, 2015 at 15:00

answered Feb 20, 2015 at 14:40

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Collectives™ on Stack Overflow

Selecting the values from a table in sql using a loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related