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]

Re: Return Stored Procudure Values

Ecohouse (vindaloo1@netz ero.com) writes:[color=blue]
> 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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ecohouse]

Re: Return Stored Procudure Values

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]

Re: Return Stored Procudure Values

Ecohouse (vindaloo1@netz ero.com) writes:[color=blue]
> Thanks for the info. But I'm still a little confused. How do I create
> and return a recordset in the stored procedure?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ecohouse]

Re: Return Stored Procudure Values

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.