Microsoft SQL Server forum - Is there any way in a sproc to LOOP thru the records of a table ?

Author

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

Radu

2005-09-21, 11:24 am

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 & #91;StoreGroupeTable
].EOF
Read [Code] from & #91;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

2005-09-21, 11:24 am

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

2005-09-21, 11:24 am

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

Thanks again ! :-))))

Alex.

--CELKO--

2005-09-22, 3:23 am

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.
[color=darkred]

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@hotmail.com

2005-09-28, 11:23 am

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

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

Sponsored Links