Iterate through rows in SQL Server 2008 -

Iterate through rows in SQL Server 2008 -

consider table sample:

id integer name nvarchar(10)

there stored proc called myproc. takes 1 paramater ( id)

given name parameter, find rows name = @nameparameter , pass ids myproc

eg:

sample-> 1 mark 2 mark 3 stu 41 mark

when mark passed, 1 ,2 , 41 passed myproc individually.

i.e. next should happen:

execute myproc 1 execute myproc 2 execute myproc 41

i can't touch myproc nor see content. have pass values it.

if must iterate(*), utilize build designed - cursor. much maligned, if expresses intentions, utilize it:

declare @id int declare ids cursor local select id sample name = @nameparameter open ids fetch next ids @id while @@fetch_status = 0 begin exec myproc @id fetch next ids @id end close ids deallocate ids

(*) reply has received few upvotes recently, sense ought incorporate original comment here also, , add together general advice:

in sql, should generally seek set-based solution. entire language oriented around set-based solutions, , (in turn) optimizer oriented around making set-based solutions work well. in farther turn, tools have available tuning optimizer set-oriented - e.g. applying indexes tables.

there few situations iteration best approach. these few far between, , may likened jackson's rules on optimization - don't - , (for experts only) don't yet.

you're far improve served first seek formulate want in terms of set of rows affected - overall alter achieved? - , seek formulate query encapsulates goal. if query produced doing not performing adequately (or there's other component unable other deal each row individually) should consider iteration.

sql sql-server-2008