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

[Radu]

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.

[Simon Hayes]

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

[Radu]

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

Thanks again ! :-))))

Alex.

[--CELKO--]

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.

[dbahooker]

plz tell me the punchline-- why do we never use underscores?

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