How can I use stored procedure's recordset output into simple SELECT statement?

Hi All
I have one stored procedure called MY_SP that returns a recordset. I would
like to use this recordset output into my SELECT statement like this:
SELECT * FROM (EXEC MY_SP '1', '2') RS
The above blurs up with syntax error at EXEC. Is there a way to achieve
this? (Note that MY_SP takes few parameters)
Thanks in advance.
NayanI don't understand why don't you just call EXEC MY_SP '1', '2'?
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have one stored procedure called MY_SP that returns a recordset. I
> would like to use this recordset output into my SELECT statement like
> this:
> SELECT * FROM (EXEC MY_SP '1', '2') RS
> The above blurs up with syntax error at EXEC. Is there a way to achieve
> this? (Note that MY_SP takes few parameters)
> Thanks in advance.
> Nayan
>|||thanks for asking
The example I have included is for the purpose of getting my problem across
to the audience. I agree with you that if I need to do something as simple
as quoted, I would rather do it your way. I'm actually trying to further
create a complex query using SELECT that will require data from this SP.
I hope I have made my question little more clearer.
TIA
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>I don't understand why don't you just call EXEC MY_SP '1', '2'?
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
>|||Use Temp table to store the resultset from MY_SP is one option.
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
> thanks for asking
> The example I have included is for the purpose of getting my problem
> across to the audience. I agree with you that if I need to do something
> as simple as quoted, I would rather do it your way. I'm actually trying
> to further create a complex query using SELECT that will require data from
> this SP.
> I hope I have made my question little more clearer.
> TIA
> Nayan
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thanks Perayu for your quick response.
The temp table option will work fine but what if I dont have permission to
modify the said SP? In that case, what would be my options?
thanks again for your ideas.
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:OfZwUV6RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Use Temp table to store the resultset from MY_SP is one option.
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
>|||On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:

>Thanks Perayu for your quick response.
>The temp table option will work fine but what if I dont have permission to
>modify the said SP? In that case, what would be my options?
Hi Nayan,
You don't have to modify the SP. You can insert the results of the SP in
a tem table, then use that in your later queries:
CREATE TABLE #Reults
(Col1 some_datetype NOT NULL,
..)
INSERT INTO #Results (Col1, ...)
EXEC EXEC MY_SP '1', '2'
Hugo Kornelis, SQL Server MVP|||In light of your other posts, rewrite the stored procedure as a table
returning function and then you can easily use it in the FROM part of a
SELECT statement.|||Hi Hugo.
Thanks for replying.
I already did this earlier but wanted know if there is a simpler way that
can allow me to pipe-in the resultset from an SP without first creating the
table.
thanks again.
Nayan
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:r7le129b97pr04pcafqo0boti13qba0945@.
4ax.com...
> On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:
>
> Hi Nayan,
> You don't have to modify the SP. You can insert the results of the SP in
> a tem table, then use that in your later queries:
> CREATE TABLE #Reults
> (Col1 some_datetype NOT NULL,
> ...)
> INSERT INTO #Results (Col1, ...)
> EXEC EXEC MY_SP '1', '2'
> --
> Hugo Kornelis, SQL Server MVP|||Hi JeffB
I understand your solution. What I'm looking at is how can I directly call
the stored procedure in the FROM part of the SELECT statement. I hope the
answer to this is not "NO" - cant use a stored procedure in the FROM part!
Thanks for the reply
Nayan
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142374607.563351.82890@.i40g2000cwc.googlegroups.com...
> In light of your other posts, rewrite the stored procedure as a table
> returning function and then you can easily use it in the FROM part of a
> SELECT statement.
>|||I don't believe that you can. This is what a function that returns a
table is for.