Results of EXEC into table?

5 Jan 2006 9:24 PM

Michael Bray

I have a feeling I already know the answer to this, but is there any way
that I can use the results of an SP in another query? In other words, I
have an SP that outputs a table, and I want to use that table as an input
to a SELECT...

-mdb

5 Jan 2006 9:37 PM

Andrew J. Kelly

http://www.sommarskog.se/share_data.html

--
Andrew J. Kelly SQL MVP

Show quote

"Michael Bray" <mbray@makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9742A6DE6C0EDmbrayctiusacom@207.46.248.16...
>I have a feeling I already know the answer to this, but is there any way
> that I can use the results of an SP in another query? In other words, I
> have an SP that outputs a table, and I want to use that table as an input
> to a SELECT...
>
> -mdb

5 Jan 2006 9:39 PM

Dan Guzman

One method is to create a target table with schema matching the proc results
and then load with INSERT...EXEC. For example:

CREATE Table #MyTable(Col1 int)

INSERT INTO #MyTable
EXEC MyProc

See the Books Online for more info.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Michael Bray" <mbray@makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9742A6DE6C0EDmbrayctiusacom@207.46.248.16...
>I have a feeling I already know the answer to this, but is there any way
> that I can use the results of an SP in another query? In other words, I
> have an SP that outputs a table, and I want to use that table as an input
> to a SELECT...
>
> -mdb