CODEWITHSUNDEEP
sql-serverwhile-loop
Arianule
Arianule

Reputation: 9043

Selecting the values from a table in sql using a loop

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?

Upvotes: 0

Views: 64

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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.

Upvotes: 1

animateme
animateme

Reputation: 309

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.

Upvotes: 1

Tanner
Tanner

Reputation: 22733

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)

Upvotes: 2

Related Questions