Iterate through rows in SQL Server 2008

Question

Consider the table SAMPLE:

id       integer
name     nvarchar(10)

There is a stored proc called myproc. It takes only one paramater ( which is id)

Given a name as parameter, find all rows with the name = @nameparameter and pass all those ids to myproc

eg:

sample->
1   mark
2   mark
3   stu
41  mark

When mark is passed, 1 ,2 and 41 are to be passed to myproc individually.

i.e. the following should happen:

execute myproc 1
execute myproc 2
execute myproc 41

I can't touch myproc nor see its content. I just have to pass the values to it.

Although this *can* be done, it's best generally avoided, usually by converting the stored proc into a UDF, or expanding it inline, so that a set based operation can be performed. We'd need to see the internals of `myproc` to advise which approach was feasible in this case. In SQL, you should *generally* seek set based solutions to problems, not loop based ones. — Damien_The_Unbeliever, Jul 07 '11 at 06:34
Simialr Question answered here http://stackoverflow.com/questions/20662356/sql-server-loop-how-do-i-loop-through-a-set-of-records/34797745#34797745 — Sandeep, Jan 14 '16 at 19:26
@Sandeep, this is an older question and I have accepted the solution — psy, Jan 15 '16 at 00:39

Damien_The_Unbeliever · Accepted Answer · 2013-04-24T18:08:20.177

If you must iterate(*), use the construct designed to do it - the cursor. Much maligned, but if it most clearly expresses your intentions, I say use it:

DECLARE @ID int
DECLARE IDs CURSOR LOCAL FOR select ID from SAMPLE where Name = @NameParameter

OPEN IDs
FETCH NEXT FROM IDs into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
    exec myproc @ID

    FETCH NEXT FROM IDs into @ID
END

CLOSE IDs
DEALLOCATE IDs

(*) This answer has received a few upvotes recently, but I feel I ought to incorporate my original comment here also, and add some general advice:

In SQL, you should generally seek a set-based solution. The entire language is oriented around set-based solutions, and (in turn) the optimizer is oriented around making set-based solutions work well. In further turn, the tools that we have available for tuning the optimizer is also set-oriented - e.g. applying indexes to tables.

There are a few situations where iteration is the best approach. These are few are far between, and may be likened to Jackson's rules on optimization - don't do it - and (for experts only) don't do it yet.

You're far better served to first try to formulate what you want in terms of the set of all rows to be affected - what is the overall change to be achieved? - and then try to formulate a query that encapsulates that goal. Only if the query produced by doing so is not performing adequately (or there's some other component that is unable to do anything other than deal with each row individually) should you consider iteration.

I want to use a cursor for sending out dbmail to recipients, which are defined by a set query, one at a time so I can catch failures. As per your second part of your answer, would this be a proper use? — DFTR, Nov 20 '14 at 20:48
@DFTR - it could be - so long as you bear in mind that you won't be able to catch all possible failures at this stage - email delivery isn't guaranteed even if everything works fine on the SQL Server side. — Damien_The_Unbeliever, Nov 21 '14 at 07:39
Yes, I thought of that. But at least I'll know it made it to our sophos, which is basically where my job ends. So... win. — DFTR, Nov 21 '14 at 20:51

rahularyansharma · Answer 2 · 2011-07-07T07:45:11.067

I just declare the temporary table @sample and insert the all rows which have the name='rahul' and also take the status column to check that the row is iterated.and using while loop i iterate through the all rows of temporary table @sample which have all the ids of name='rahul'

use dumme

Declare @Name nvarchar(50)
set @Name='Rahul'
DECLARE @sample table (

    ID int,
    Status varchar(500)

    )
insert into @sample (ID,status) select ID,0 from sample where sample=@name
while ((select count(Id) from @sample where status=0 )>0) 
begin
    select top 1  Id  from @sample where status=0 order by Id
    update @sample set status=1  where Id=(select top 1  Id  from @sample where status=0 order by Id) 
end

You could declare ID as Identity(1,1) and loop over it without having to update it. — Aniket Inge, Apr 09 '15 at 01:02

score -2 · Answer 3 · edited Jul 07 '11 at 07:03

-2

Declare @retStr varchar(100)

select @retStr = COALESCE(@retStr, '') + sample.ID + ', '
from sample 
WHERE sample.Name = @nameparameter 
select  @retStr = ltrim(rtrim(substring(@retStr , 1, len(@retStr )- 1)))

Return  ISNULL(@retStr ,'')

edited Jul 07 '11 at 07:03

marc_s

732,580
175
1,330
1,459

answered Jul 07 '11 at 06:35

Kushal Shah

55
2
6

Iterate through rows in SQL Server 2008

3 Answers3

Linked