Is there any way in a sproc to LOOP thru the records of a table ?

Hi. It seems to be very simple, actually, but I don't know if it is
feasible in TSQL. I have a sproc which gathers in one place many calls
to different other sprocs, all of them taking a 'StoreGroupe'
parameter. I would like to add a case where if the call has NO
StoreGroupe parameter, the sproc should LOOP thru all records in table
StoreGroupeTable, read the column StoreCode, and pass that value as a
param to the other sprocs, as in:

CREATE PROCEDURE MySproc

(
@.StoreGroupe nvarchar(6) = NULL
)

AS
if (@.StoreGroupe is not null)
Begin
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
End
Else
Begin
A 'Group Code' has NOT been specified
I want to take all the StoreGroups in table
StoreGroupeTable, in turn.
I would like to do SOMETHING LIKE THIS:
Do While not [StoreGroupeTable].EOF
Read [Code] from [StoreGroupeTable]
Set @.StoreGroupe = The value I just read
Exec _Sproc1 @.StoreGroupe
Exec _Sproc2 @.StoreGroupe
Exec _Sproc3 @.StoreGroupe
Exec _Sproc4 @.StoreGroupe
............
Loop
End
GO

Is that feasible in a sproc, or do I have to do this in the client
(ADO) ?

Thanks a lot.
Alex.You can do this using a cursor - executing a stored proc repeatedly for
each value in a column is one of the (few) cases where they're useful.
In performance terms it would probably be better to rewrite proc1,
proc2 etc. to operate on a set of values using set-based code (but that
may not be possible, of course, depending on what the procs are doing).

declare @.StoreGroupe int

declare cur cursor local static
for select SomeCode from dbo.StoreGroupes

open cur
fetch next from cur into @.StoreGroupe
while @.@.fetch_status = 0
begin
exec dbo.proc1 @.StoreGroupe
exec dbo.proc2 @.StoreGroupe
-- etc.
fetch next from cur into @.StoreGroupe
end

close cur
deallocate cur

Simon|||Thanks a lot, Simon, I'll implement this right now.

Thanks again ! :-))))

Alex.|||You have not learned to think in SQL yet and are still writing 3GL
procedural code. Let's get back to the basics of an RDBMS. Rows are not
records; fields are not columns; tables are not files.

>> I have a sproc which gathers in one place many calls to different other sprocs, all of them taking a 'StoreGroupe' parameter. <<

Why?? You have forgotten or never learned the very Basics of
programming. Remember why we NEVER begin a name with an underscore?
Remember coupling and cohesion in your first Software Engineering
class?

Since you did not tell us anything about the modules, give us DDL or
even a hint of a specification, it is impossible to tell exactly what
is happening, but these are the basics. However, each module should
handle a NULL parameter on its own. If you reallllly want to write
stinking bad code, then use a CURSOR, keep writing separate modules
that are incomplete, etc.

If you post more specs, you will get more help. Otherwise yiou will
get kludges.|||plz tell me the punchline-- why do we never use underscores?

ps - i agree, loops on the TSQL side are for newbies