Return Stored Procudure Values

[Ecohouse]

I have a quick question for you about SQL stored procedures. If I'm
in a stored procedure and want to call another stored procedure and
return values from the second stored procedure what is the procedure?

I know you do the following to run the second stored procedure and pass
in any parameters:
EXEC GetAuthorBooks @AuthorID

So if I wanted the GetAuthorBooks to return all the books for an author
and then populate a temp table in the original stored procedure, how do
I return those records, and populate the temp table?

What do I have to add to this line: EXEC GetAuthorBooks @AuthorID?

[Erland Sommarskog]

Ecohouse (vi*******@netz ero.com) writes:

I have a quick question for you about SQL stored procedures. If I'm
in a stored procedure and want to call another stored procedure and
return values from the second stored procedure what is the procedure?

I know you do the following to run the second stored procedure and pass
in any parameters:
EXEC GetAuthorBooks @AuthorID

So if I wanted the GetAuthorBooks to return all the books for an author
and then populate a temp table in the original stored procedure, how do
I return those records, and populate the temp table?

What do I have to add to this line: EXEC GetAuthorBooks @AuthorID?

There are a couple of methods, and I have an article on the topic on
my web site: http://www.sommarskog.se/share_data.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ecohouse]

Thanks for the info. But I'm still a little confused. How do I create
and return a recordset in the stored procedure?

[Erland Sommarskog]

Ecohouse (vi*******@netz ero.com) writes:

Thanks for the info. But I'm still a little confused. How do I create
and return a recordset in the stored procedure?

I'm not really sure in which context you are asking this question, but to
return a result set from a stored procedure, you simply issue a SELECT
statement.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ecohouse]

Thanks for the help. I was able to figure it out. I didn't realize
that once a temp table is created it's global and can be accessed from
another sp and stays active until the original sp finishes running.