loop though values in SQL

Question

the select statement below slects a list of test id's

select testID from tblTests

I have the code blow that updates results based on the test ID. I would like to loop through the above code so that i can loop through one ID at a time. How would i loop though all of the test ID's from the statement above?

--get decision from tbloptions
declare @decision nvarchar
declare @newQuestionNo char(10)
declare @suffix char(1)
declare @scenarioID int
declare @scenarioNo int
declare @testsummaryid int
declare @points int

select 
    @decision = tbloptions.decision,
    @newQuestionNo = rtrim(tbloptions.GoToQuestion),
    @points = coalesce(tbloptions.points,0),
    @suffix = tbloptions.GoToSuffix,
    @scenarioID = tbloptions.scenarioID,
    @testsummaryid = testsummaryid 
from 
    tbloptions with(nolock)
    join tbltests on tbloptions.scenarioID = tbltests.scenarioID
        and tbloptions.questionNo = tbltests.questionNo
where 
    tbltests.testid = 


if @newQuestionNo in ('Pass','Neutral','Fail')
begin
--end scenario session
    update tblTestSummaries
    set 
        end_date = getdate(),
        points = @points,
        completed = 1,
        timetaken = dbo.TimeTaken(
            (select 
                start_date 
            from tbltestsummaries
            where testsummaryid = @testsummaryid),
            getdate()),
        result = case 
            when @points > 2 then 'P'
            when @points = 2 then 'I'
            when @points < 2 then 'F'
        end, 
        testSessionID = @testSessionID
    where testsummaryid = @testsummaryid
end

change `where tbltests.testid =` to `where tbltests.testid IN (select testID from tblTests)`. Hurrah! no looping required! — Nick.Mc, Apr 14 '15 at 10:00
if you HAVE to loop, then you will need to create a CURSOR based on your SELECT statement and loop using the cursor — Mike, Apr 14 '15 at 10:14
after they have selected i need to do a update. nut all test suammryID are uniquq — user3636426, Apr 14 '15 at 10:17
tghat will not work becuase the testsummary ids are a froign key, and the select has multiple test-id, which has difffernt testid. — user3636426, Apr 14 '15 at 11:02

score 0 · Answer 1 · edited May 23 '17 at 10:10

SQL Server is optimized to perform set-based operations. When you try to apply iterative logic, you reduce performance. If you're coming from a coding background (or an ISAM database background) thinking in a set-based, relational database way can be tricky at first. If you find yourself looking for an iterative solution or thinking that you need to use a cursor, then you may want to take a step back and ask yourself if you're really doing this in the most efficient way.

The following query should do what you're looking for and still allow SQL Server to optimize the query. Please take note of the comments in the query.

UPDATE  TS
SET     end_date = getdate(),
        points = coalesce(O.points,0),
        completed = 1,
        timetaken = dbo.TimeTaken(start_date, getdate()),
        result = case 
            when coalesce(O.points,0) > 2 then 'P'
            when coalesce(O.points,0) = 2 then 'I'
            when coalesce(O.points,0) < 2 then 'F'
        end, 
        testSessionID = @testSessionID -- I did not see this variable anywhere else in your code and am assuming it's defined elsewhere.
FROM    tbloptions O --with(nolock)  Be Careful using this.  Read about "dirty reads" below.
JOIN    tbltests T on O.scenarioID = T.scenarioID and O.questionNo = T.questionNo
JOIN    tblTestSummaries TS ON TS.testSummaryID = T.testSummaryID
WHERE   rtrim(tbloptions.GoToQuestion) IN ('Pass', 'Neutral', 'Fail') -- Note that this is not Sargable and may not perform well. Consider changing the varchar field to a FK to remove the need to trim the text.

loop though values in SQL

1 Answers1