Return selected fields from an EXECUTE?

Return selected fields from an EXECUTE?

Post by Cavema » Sun, 10 Nov 2002 04:35:34



Is there a way to return from sproc2 only specific fields
from an EXECUTE sproc1, which sproc1 returns a return set,
WITHOUT loading the set into a temp table in sproc2 and
selecting the desired fields from it?  UDFs are not an
option since I am using non-deterministic functions in
sproc1.

Right now I have

CREATE PROCEDURE proc2
AS
CREATE TABLE #mytable...

INSERT INTO #mytable
(columns I want)
EXECUTE proc1

SELECT *
FROM #mytable

------------

What I want tis something like
CREATE PROCEDURE proc2
AS
CREATE TABLE #mytable...

SELECT columns_I_want
EXECUTE proc1

Thanks,
Kurt

 
 
 

Return selected fields from an EXECUTE?

Post by Tony Rogerso » Sun, 10 Nov 2002 05:00:09


Basically the answer is no.

You need to push the output from the proc into a temporary # or ## table
first.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]


Quote:> Is there a way to return from sproc2 only specific fields
> from an EXECUTE sproc1, which sproc1 returns a return set,
> WITHOUT loading the set into a temp table in sproc2 and
> selecting the desired fields from it?  UDFs are not an
> option since I am using non-deterministic functions in
> sproc1.

> Right now I have

> CREATE PROCEDURE proc2
> AS
> CREATE TABLE #mytable...

> INSERT INTO #mytable
> (columns I want)
> EXECUTE proc1

> SELECT *
> FROM #mytable

> ------------

> What I want tis something like
> CREATE PROCEDURE proc2
> AS
> CREATE TABLE #mytable...

> SELECT columns_I_want
> EXECUTE proc1

> Thanks,
> Kurt


 
 
 

Return selected fields from an EXECUTE?

Post by Anith Se » Sun, 10 Nov 2002 08:56:52


If efficiency is not your primary concern, you can try
using 'data access' property with sp_serveroption set to true
and try a distributed Query like:

EXEC sp_serveroption 'yourServer','data access','true'
GO
SELECT  col1, col2, col3...
  FROM OPENQUERY(<yourServer>,'EXEC yourStoredProc')

--
- Anith