a stored proc calling another stored proc 
Author Message
 a stored proc calling another stored proc
I'd like to write a stored proc which gets a list of IDs from the table:

    select ID from users where organizationID = 101

Then for each ID, I'd like to call another stored procedure to create
records.

    exec spCreateProfile ID

How do I do this in a stored proc?



Sat, 04 Sep 2004 23:28:53 GMT
 a stored proc calling another stored proc

Within your main SP, either open a cursor or create a temp table (then
populate it with insert into).  Then cycle through each record, calling your
other proc.  Here's an example using a temp table:


as

--create temp table
create table #Users
(userID int)
--populate table

--set up loop

--loop through each record

  begin


  end
--clean up (not really necassary, but good habit)
drop table #Users

You can also do it with a cursor - see books on line for an example

HTH

Quote:

> I'd like to write a stored proc which gets a list of IDs from the table:

>     select ID from users where organizationID = 101

> Then for each ID, I'd like to call another stored procedure to create
> records.

>     exec spCreateProfile ID

> How do I do this in a stored proc?



Sun, 05 Sep 2004 00:30:02 GMT
 a stored proc calling another stored proc
Thanks.

What's more efficient, the cursor approach or the temp table?


Quote:
> Within your main SP, either open a cursor or create a temp table (then
> populate it with insert into).  Then cycle through each record, calling
your
> other proc.  Here's an example using a temp table:


> as

> --create temp table
> create table #Users
> (userID int)
> --populate table

> --set up loop

> --loop through each record

>   begin


>   end
> --clean up (not really necassary, but good habit)
> drop table #Users

> You can also do it with a cursor - see books on line for an example

> HTH




- Show quoted text -

Quote:
> > I'd like to write a stored proc which gets a list of IDs from the table:

> >     select ID from users where organizationID = 101

> > Then for each ID, I'd like to call another stored procedure to create
> > records.

> >     exec spCreateProfile ID

> > How do I do this in a stored proc?



Mon, 06 Sep 2004 04:37:18 GMT
 a stored proc calling another stored proc
To be honest, I'm not sure.  If performance is important, try both.  I have
found the temp table method easier to maintain and read, but that's personal
preference.  One possible advantage with cursors is that you can use a
cursor on the client, where temp tables reside on the server.

Sorry I can't provide more info.  Perhaps someone else has some input.

Quote:

> Thanks.

> What's more efficient, the cursor approach or the temp table?



> > Within your main SP, either open a cursor or create a temp table (then
> > populate it with insert into).  Then cycle through each record, calling
> your
> > other proc.  Here's an example using a temp table:


> > as

> > --create temp table
> > create table #Users
> > (userID int)
> > --populate table

> > --set up loop

> > --loop through each record

> >   begin


> >   end
> > --clean up (not really necassary, but good habit)
> > drop table #Users

> > You can also do it with a cursor - see books on line for an example

> > HTH



> > > I'd like to write a stored proc which gets a list of IDs from the
table:

> > >     select ID from users where organizationID = 101

> > > Then for each ID, I'd like to call another stored procedure to create
> > > records.

> > >     exec spCreateProfile ID

> > > How do I do this in a stored proc?



Mon, 06 Sep 2004 07:45:56 GMT
 a stored proc calling another stored proc
One of the books told me to "avoid the use of cursors whenever possible"
because it degrades performance.


Quote:
> To be honest, I'm not sure.  If performance is important, try both.  I
have
> found the temp table method easier to maintain and read, but that's
personal
> preference.  One possible advantage with cursors is that you can use a
> cursor on the client, where temp tables reside on the server.

> Sorry I can't provide more info.  Perhaps someone else has some input.




Quote:
> > Thanks.

> > What's more efficient, the cursor approach or the temp table?



> > > Within your main SP, either open a cursor or create a temp table (then
> > > populate it with insert into).  Then cycle through each record,
calling
> > your
> > > other proc.  Here's an example using a temp table:


> > > as

> > > --create temp table
> > > create table #Users
> > > (userID int)
> > > --populate table

> > > --set up loop

> > > --loop through each record

> > >   begin


> > >   end
> > > --clean up (not really necassary, but good habit)
> > > drop table #Users

> > > You can also do it with a cursor - see books on line for an example

> > > HTH



> > > > I'd like to write a stored proc which gets a list of IDs from the
> table:

> > > >     select ID from users where organizationID = 101

> > > > Then for each ID, I'd like to call another stored procedure to
create
> > > > records.

> > > >     exec spCreateProfile ID

> > > > How do I do this in a stored proc?



Mon, 06 Sep 2004 23:16:57 GMT
 a stored proc calling another stored proc
I've read that too, but I've also read to avoid Temp Tables whenever
possible.  In your case, it sounds like you need to do one or the other.
I've started a new thread in comp.databases.theory.  I'll probably get
flamed for asking such a general question in that group, but we'll probably
get a lot of useful information if anyone responds.

BTW, have you tried the nested SP yet?  If so, I assume you went with the
temp table approach - how did it work?

Quote:

> One of the books told me to "avoid the use of cursors whenever possible"
> because it degrades performance.



> > To be honest, I'm not sure.  If performance is important, try both.  I
> have
> > found the temp table method easier to maintain and read, but that's
> personal
> > preference.  One possible advantage with cursors is that you can use a
> > cursor on the client, where temp tables reside on the server.

> > Sorry I can't provide more info.  Perhaps someone else has some input.



> > > Thanks.

> > > What's more efficient, the cursor approach or the temp table?



> > > > Within your main SP, either open a cursor or create a temp table
(then
> > > > populate it with insert into).  Then cycle through each record,
> calling
> > > your
> > > > other proc.  Here's an example using a temp table:


> > > > as

> > > > --create temp table
> > > > create table #Users
> > > > (userID int)
> > > > --populate table

> > > > --set up loop

> > > > --loop through each record

> > > >   begin


> > > >   end
> > > > --clean up (not really necassary, but good habit)
> > > > drop table #Users

> > > > You can also do it with a cursor - see books on line for an example

> > > > HTH



> > > > > I'd like to write a stored proc which gets a list of IDs from the
> > table:

> > > > >     select ID from users where organizationID = 101

> > > > > Then for each ID, I'd like to call another stored procedure to
> create
> > > > > records.

> > > > >     exec spCreateProfile ID

> > > > > How do I do this in a stored proc?



Tue, 07 Sep 2004 00:07:17 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Stored Proc Calling Another Stored Proc

2. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

3. Stored Proc Call in Stored Proc

4. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

5. calling stored proc from stored proc via variable

6. Calling a stored proc within a stored proc

7. Calling Stored Proc from other Stored Proc

8. Calling another stored proc within a stored proc

9. Using a stored proc to call another stored proc

10. Calling a Stored proc within a stored proc

11. Calling Stored Proc from another Stored Proc

12. Calling stored proc from stored proc


 
Powered by phpBB® Forum Software