selecting just a column from a resultset returned by an SP

microsoft.public.sqlserver.programming

16 Sep 2005 9:20 AM

joeycalisay

can i do something like this:

SELECT BidID FROM (EXEC BidAccessRetrieve @BidID = 30, @LevelID = 6)

where BidAccessRetrieve is an SP which returns a resultset? I just
want to reuse the said SP to obtain the list of BidIDs which I will use
in an IN clause of another SP, I hope I am making sense here...

16 Sep 2005 9:31 AM

R.D

Hi
No
use insert into <temptable> and select from there.
or use function where you can select asif from view
or return table from sp
Regards
R.D

Show quote

"joeycalisay" wrote:

> can i do something like this:
>
> SELECT BidID FROM (EXEC BidAccessRetrieve @BidID = 30, @LevelID = 6)
>
> where BidAccessRetrieve is an SP which returns a resultset? I just
> want to reuse the said SP to obtain the list of BidIDs which I will use
> in an IN clause of another SP, I hope I am making sense here...
>
>

16 Sep 2005 9:34 AM

David Portas

CREATE TABLE #BidAccessRetrieve (bidid INTEGER, ...)

INSERT INTO #BidAccessRetrieve (bidid, ...)
EXEC BidAccessRetrieve @BidID = 30, @LevelID = 6

SELECT ...
FROM ...
WHERE bidid IN
(SELECT bidid
FROM #BidAccessRetrieve)

DROP TABLE #BidAccessRetrieve

--
David Portas
SQL Server MVP
--

16 Sep 2005 9:51 AM

joeycalisay

thanks guys, i'll try it out...

16 Sep 2005 11:23 AM

There is another way - a more resource-intensive way - using OPENQUERY.

The preferred way, though, would be to include the processing logic of the
subprocedure in the calling procedure.

ML