Stored Proc Calling Another Stored Proc

Stored Proc Calling Another Stored Proc

Post by NoSpamPl » Thu, 02 Jan 2003 03:30:24



I have a table which has a child/parent relationship. I wrote a stored
procedure that returns all the children for any given record. I now want to
use this stored procedure in another stored procedure that will delete the
records retrieved from the first stored procedure. It sounds so simple, but
I'm stumped. How can I store the results from the first stored procedure
into a cursor that I can work with in the second stored procedure?
 
 
 

Stored Proc Calling Another Stored Proc

Post by BP Margoli » Thu, 02 Jan 2003 03:49:03


use pubs
go

create procedure p
as
select au_lname, au_fname
from authors
go

create table #a
(
 au_lname varchar (40) NOT NULL,
 au_fname varchar (20) NOT NULL
)
go

insert into #a
exec p

select * from #a
go

-- clean up
drop procedure p
drop table #a

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I have a table which has a child/parent relationship. I wrote a stored
> procedure that returns all the children for any given record. I now want
to
> use this stored procedure in another stored procedure that will delete the
> records retrieved from the first stored procedure. It sounds so simple,
but
> I'm stumped. How can I store the results from the first stored procedure
> into a cursor that I can work with in the second stored procedure?


 
 
 

Stored Proc Calling Another Stored Proc

Post by NoSpamPl » Thu, 02 Jan 2003 05:22:22


Sorry, this isnt' really what I'm looking for.


> use pubs
> go

> create procedure p
> as
> select au_lname, au_fname
> from authors
> go

> create table #a
> (
>  au_lname varchar (40) NOT NULL,
>  au_fname varchar (20) NOT NULL
> )
> go

> insert into #a
> exec p

> select * from #a
> go

> -- clean up
> drop procedure p
> drop table #a

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I have a table which has a child/parent relationship. I wrote a stored
> > procedure that returns all the children for any given record. I now want
> to
> > use this stored procedure in another stored procedure that will delete
the
> > records retrieved from the first stored procedure. It sounds so simple,
> but
> > I'm stumped. How can I store the results from the first stored procedure
> > into a cursor that I can work with in the second stored procedure?

 
 
 

Stored Proc Calling Another Stored Proc

Post by John Bel » Thu, 02 Jan 2003 05:32:49


Hi

You don't say what version of SQL Server you are using, but for SQL2000 if
you implemented foreign key constraints on the "child" table then you could
use cascading deletes and there would not be any need to do this.

To understand what you are trying to obtain further please post your DDL to
create the tables concerned, example data using insert statements and a copy
of the current stored procedure.

John


Quote:> I have a table which has a child/parent relationship. I wrote a stored
> procedure that returns all the children for any given record. I now want
to
> use this stored procedure in another stored procedure that will delete the
> records retrieved from the first stored procedure. It sounds so simple,
but
> I'm stumped. How can I store the results from the first stored procedure
> into a cursor that I can work with in the second stored procedure?

 
 
 

Stored Proc Calling Another Stored Proc

Post by todderama » Thu, 02 Jan 2003 05:50:19


Hi,

I don't use SQL Server, but I would do with our database is:

In the second stored procedure call the first procedure in a loop.  For
every record returned by the first procedure, just simply use a delete
statement to delete the record.  In Firebird it looks like:

FOR SELECT ID FROM STOREDPROCEDURE(:INPUTVARIABLE) INTO :ID
DO
BEGIN
        DELETE FROM TABLE WHERE ID = :ID;
END


> I have a table which has a child/parent relationship. I wrote a stored
> procedure that returns all the children for any given record. I now want to
> use this stored procedure in another stored procedure that will delete the
> records retrieved from the first stored procedure. It sounds so simple, but
> I'm stumped. How can I store the results from the first stored procedure
> into a cursor that I can work with in the second stored procedure?

 
 
 

Stored Proc Calling Another Stored Proc

Post by BP Margoli » Thu, 02 Jan 2003 06:07:20


Well, it might help if you perhaps posted some elaboration on what it is you
are looking for   :-)

In case it's not clear, once you have the temp table populated, you can then
create a cursor pulling data from the temp table.

An alternative that can sometimes work is to create a self-referencing
linked server and then use OPENQUERY to pull in the result set returned from
the stored procedure ... for example, assuming that your server is named
Swoosh then something like:

EXEC sp_serveroption Swoosh, 'data access' , 'true'

use pubs
select * from OPENQUERY(Swoosh,'select * from pubs.dbo.authors')

Again, you can create a cursor to surround this SELECT just like you can
with the temp table technique.

Personally, between these two techniques, I'd favor using the temp table
one.

Finally, as indicated by John, you should ALWAYS post which version of SQL
Server you are using. If you are using SQL Server 2000, consider changing
the stored procedure to a user defined function that returns a table.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Sorry, this isnt' really what I'm looking for.



> > use pubs
> > go

> > create procedure p
> > as
> > select au_lname, au_fname
> > from authors
> > go

> > create table #a
> > (
> >  au_lname varchar (40) NOT NULL,
> >  au_fname varchar (20) NOT NULL
> > )
> > go

> > insert into #a
> > exec p

> > select * from #a
> > go

> > -- clean up
> > drop procedure p
> > drop table #a

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I have a table which has a child/parent relationship. I wrote a stored
> > > procedure that returns all the children for any given record. I now
want
> > to
> > > use this stored procedure in another stored procedure that will delete
> the
> > > records retrieved from the first stored procedure. It sounds so
simple,
> > but
> > > I'm stumped. How can I store the results from the first stored
procedure
> > > into a cursor that I can work with in the second stored procedure?

 
 
 

Stored Proc Calling Another Stored Proc

Post by Erland Sommarsko » Thu, 02 Jan 2003 06:31:09



> I have a table which has a child/parent relationship. I wrote a stored
> procedure that returns all the children for any given record. I now want
> to use this stored procedure in another stored procedure that will
> delete the records retrieved from the first stored procedure. It sounds
> so simple, but I'm stumped. How can I store the results from the first
> stored procedure into a cursor that I can work with in the second stored
> procedure?

First of all, avoid cursors as much as you can. Try always to use set-
based solutions. This can make a *magnitude* in difference in performance.

The easiest might simply be to delete all the rows in the child
tables in one DELETE statement.

This may not agree with what you bave learnt from 3GL programming,
where you try to hide data, and write small routines which all does
its little job. This is to some extent a virute in SQL too, but one
should not take it too far.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org

 
 
 

Stored Proc Calling Another Stored Proc

Post by D Newto » Thu, 02 Jan 2003 06:57:51


Check out the SQL help file for returning an Output variable from your sproc.  Since the return
variable is a Cursor, you'd need to use the CURSOR VARYING OUTPUT:


Then your sproc can return a cursor to an outer procedure which is calling it.

But consider approaching this from the reverse:  given a parent ID, call a subroutine sproc which
deletes the children.  Then just pass that sproc the parent ID foreign key.


Quote:> I have a table which has a child/parent relationship. I wrote a stored
> procedure that returns all the children for any given record. I now want to
> use this stored procedure in another stored procedure that will delete the
> records retrieved from the first stored procedure. It sounds so simple, but
> I'm stumped. How can I store the results from the first stored procedure
> into a cursor that I can work with in the second stored procedure?

 
 
 

Stored Proc Calling Another Stored Proc

Post by JB » Thu, 02 Jan 2003 07:26:02


Sorry, actually, I was too quick to dismiss your proposed solution. (Due to
the frustration I was having : )

I actually ended up implementing it with a temp table and it works like a
charm.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE   PROC dbo.DeletePoss

AS

SET NOCOUNT ON

/* Check if the temp table exists, and remove it */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
'#DeletePoss')
    DROP TABLE #DeletePoss

/* Now create the table */
CREATE TABLE #DeletePoss  (SortID int, PossID int, ParentID int,
LastModified varchar(19), AuthorID int)

/* Populate the temporary table with the child Poss records we need to
remove*/
INSERT INTO #DeletePoss

/* Add the Parent to the table as well. */
INSERT INTO #DeletePoss (PostID)

/* Flush the toilet */
DELETE FROM Poss
WHERE Poss IN (SELECT PossID FROM #DeletePosts)

DROP table #DeletePoss

SET NOCOUNT OFF

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


> Well, it might help if you perhaps posted some elaboration on what it is
you
> are looking for   :-)

> In case it's not clear, once you have the temp table populated, you can
then
> create a cursor pulling data from the temp table.

> An alternative that can sometimes work is to create a self-referencing
> linked server and then use OPENQUERY to pull in the result set returned
from
> the stored procedure ... for example, assuming that your server is named
> Swoosh then something like:

> EXEC sp_serveroption Swoosh, 'data access' , 'true'

> use pubs
> select * from OPENQUERY(Swoosh,'select * from pubs.dbo.authors')

> Again, you can create a cursor to surround this SELECT just like you can
> with the temp table technique.

> Personally, between these two techniques, I'd favor using the temp table
> one.

> Finally, as indicated by John, you should ALWAYS post which version of SQL
> Server you are using. If you are using SQL Server 2000, consider changing
> the stored procedure to a user defined function that returns a table.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Sorry, this isnt' really what I'm looking for.



> > > use pubs
> > > go

> > > create procedure p
> > > as
> > > select au_lname, au_fname
> > > from authors
> > > go

> > > create table #a
> > > (
> > >  au_lname varchar (40) NOT NULL,
> > >  au_fname varchar (20) NOT NULL
> > > )
> > > go

> > > insert into #a
> > > exec p

> > > select * from #a
> > > go

> > > -- clean up
> > > drop procedure p
> > > drop table #a

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > I have a table which has a child/parent relationship. I wrote a
stored
> > > > procedure that returns all the children for any given record. I now
> want
> > > to
> > > > use this stored procedure in another stored procedure that will
delete
> > the
> > > > records retrieved from the first stored procedure. It sounds so
> simple,
> > > but
> > > > I'm stumped. How can I store the results from the first stored
> procedure
> > > > into a cursor that I can work with in the second stored procedure?

 
 
 

Stored Proc Calling Another Stored Proc

Post by BP Margoli » Thu, 02 Jan 2003 11:18:16


JB,

Glad it helped ... BTW, enjoyed your documentation: "Flush the toilet"   :-)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Sorry, actually, I was too quick to dismiss your proposed solution. (Due
to
> the frustration I was having : )

> I actually ended up implementing it with a temp table and it works like a
> charm.

> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO

> CREATE   PROC dbo.DeletePoss


> AS

> SET NOCOUNT ON

> /* Check if the temp table exists, and remove it */
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
> '#DeletePoss')
>     DROP TABLE #DeletePoss

> /* Now create the table */
> CREATE TABLE #DeletePoss  (SortID int, PossID int, ParentID int,
> LastModified varchar(19), AuthorID int)

> /* Populate the temporary table with the child Poss records we need to
> remove*/
> INSERT INTO #DeletePoss

> /* Add the Parent to the table as well. */
> INSERT INTO #DeletePoss (PostID)

> /* Flush the toilet */
> DELETE FROM Poss
> WHERE Poss IN (SELECT PossID FROM #DeletePosts)

> DROP table #DeletePoss

> SET NOCOUNT OFF

> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO



> > Well, it might help if you perhaps posted some elaboration on what it is
> you
> > are looking for   :-)

> > In case it's not clear, once you have the temp table populated, you can
> then
> > create a cursor pulling data from the temp table.

> > An alternative that can sometimes work is to create a self-referencing
> > linked server and then use OPENQUERY to pull in the result set returned
> from
> > the stored procedure ... for example, assuming that your server is named
> > Swoosh then something like:

> > EXEC sp_serveroption Swoosh, 'data access' , 'true'

> > use pubs
> > select * from OPENQUERY(Swoosh,'select * from pubs.dbo.authors')

> > Again, you can create a cursor to surround this SELECT just like you can
> > with the temp table technique.

> > Personally, between these two techniques, I'd favor using the temp table
> > one.

> > Finally, as indicated by John, you should ALWAYS post which version of
SQL
> > Server you are using. If you are using SQL Server 2000, consider
changing
> > the stored procedure to a user defined function that returns a table.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Sorry, this isnt' really what I'm looking for.



> > > > use pubs
> > > > go

> > > > create procedure p
> > > > as
> > > > select au_lname, au_fname
> > > > from authors
> > > > go

> > > > create table #a
> > > > (
> > > >  au_lname varchar (40) NOT NULL,
> > > >  au_fname varchar (20) NOT NULL
> > > > )
> > > > go

> > > > insert into #a
> > > > exec p

> > > > select * from #a
> > > > go

> > > > -- clean up
> > > > drop procedure p
> > > > drop table #a

> > > > -------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > which
> > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > I have a table which has a child/parent relationship. I wrote a
> stored
> > > > > procedure that returns all the children for any given record. I
now
> > want
> > > > to
> > > > > use this stored procedure in another stored procedure that will
> delete
> > > the
> > > > > records retrieved from the first stored procedure. It sounds so
> > simple,
> > > > but
> > > > > I'm stumped. How can I store the results from the first stored
> > procedure
> > > > > into a cursor that I can work with in the second stored procedure?

 
 
 

Stored Proc Calling Another Stored Proc

Post by NoSpamPl » Fri, 03 Jan 2003 00:40:49


LOL, thanks again. As I tell my team, the code is boring to look at, so I
have to do something in order to keep myself amused : )


> JB,

> Glad it helped ... BTW, enjoyed your documentation: "Flush the toilet"
:-)

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Sorry, actually, I was too quick to dismiss your proposed solution. (Due
> to
> > the frustration I was having : )

> > I actually ended up implementing it with a temp table and it works like
a
> > charm.

> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_NULLS ON
> > GO

> > CREATE   PROC dbo.DeletePoss


> > AS

> > SET NOCOUNT ON

> > /* Check if the temp table exists, and remove it */
> > IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables WHERE table_name =
> > '#DeletePoss')
> >     DROP TABLE #DeletePoss

> > /* Now create the table */
> > CREATE TABLE #DeletePoss  (SortID int, PossID int, ParentID int,
> > LastModified varchar(19), AuthorID int)

> > /* Populate the temporary table with the child Poss records we need to
> > remove*/
> > INSERT INTO #DeletePoss

> > /* Add the Parent to the table as well. */
> > INSERT INTO #DeletePoss (PostID)

> > /* Flush the toilet */
> > DELETE FROM Poss
> > WHERE Poss IN (SELECT PossID FROM #DeletePosts)

> > DROP table #DeletePoss

> > SET NOCOUNT OFF

> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO



> > > Well, it might help if you perhaps posted some elaboration on what it
is
> > you
> > > are looking for   :-)

> > > In case it's not clear, once you have the temp table populated, you
can
> > then
> > > create a cursor pulling data from the temp table.

> > > An alternative that can sometimes work is to create a self-referencing
> > > linked server and then use OPENQUERY to pull in the result set
returned
> > from
> > > the stored procedure ... for example, assuming that your server is
named
> > > Swoosh then something like:

> > > EXEC sp_serveroption Swoosh, 'data access' , 'true'

> > > use pubs
> > > select * from OPENQUERY(Swoosh,'select * from pubs.dbo.authors')

> > > Again, you can create a cursor to surround this SELECT just like you
can
> > > with the temp table technique.

> > > Personally, between these two techniques, I'd favor using the temp
table
> > > one.

> > > Finally, as indicated by John, you should ALWAYS post which version of
> SQL
> > > Server you are using. If you are using SQL Server 2000, consider
> changing
> > > the stored procedure to a user defined function that returns a table.

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > Sorry, this isnt' really what I'm looking for.



> > > > > use pubs
> > > > > go

> > > > > create procedure p
> > > > > as
> > > > > select au_lname, au_fname
> > > > > from authors
> > > > > go

> > > > > create table #a
> > > > > (
> > > > >  au_lname varchar (40) NOT NULL,
> > > > >  au_fname varchar (20) NOT NULL
> > > > > )
> > > > > go

> > > > > insert into #a
> > > > > exec p

> > > > > select * from #a
> > > > > go

> > > > > -- clean up
> > > > > drop procedure p
> > > > > drop table #a

> > > > > -------------------------------------------
> > > > > BP Margolin
> > > > > Please reply only to the newsgroups.
> > > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ...,
etc.)
> > > which
> > > > > can be cut and pasted into Query Analyzer is appreciated.



> > > > > > I have a table which has a child/parent relationship. I wrote a
> > stored
> > > > > > procedure that returns all the children for any given record. I
> now
> > > want
> > > > > to
> > > > > > use this stored procedure in another stored procedure that will
> > delete
> > > > the
> > > > > > records retrieved from the first stored procedure. It sounds so
> > > simple,
> > > > > but
> > > > > > I'm stumped. How can I store the results from the first stored
> > > procedure
> > > > > > into a cursor that I can work with in the second stored
procedure?

 
 
 

1. Saving the resultset from a stored proc being called in another stored proc

Here's my problem:

I have a stored proc on a server where the only permission
I have is to execute a certain stored proc.  I would like
to be able to create a stored proc on a box I am dbo on
and execute the remote stored proc and save the result set
into a temp or perm table.  I've already ran
sp_addlinkedserver on my box so I can execute the remote
stored proc from my box and get a result set in query
analyzer.  I'd like to be able to save the result set off
into a table so I can further manipulate it without
pinging the remote server again.

Any help would be appreciated.

2. how do i just store the last line of output?

3. Calling a stored proc within a stored proc

4. ado open problem.

5. calling stored proc from stored proc via variable

6. Chicago: Lead Essbase Developer Needed

7. How do I call a stored proc from inside another stored proc

8. Storing French and Spanish names in a database

9. a stored proc calling another stored proc

10. Stored Proc Call in Stored Proc

11. Calling another stored proc within a stored proc

12. Using a stored proc to call another stored proc

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