can I execute a sp in a select statment 2

[mercwrought]

Can you execute a sp in a select statement?

Like…

select * from ( sp_whatever 3456) as XX

 

[jbenson001]

I think what you are looking for here is a user defined function (UDF)

 

[Juice05]

Or just another select statement

 

[mercwrought]

the udf is exactly what i am looking for. THX

 

[mercwrought]

ok are there UDF s in sql server 7?

 

[jbenson001]

No there are not. It is a new feature of SQL Server 2000.

 

[mercwrought]

only a few more month to upgrage to 2003 yea!

ok I found my sultion {CALL sp_whatever (123)}

thx for all of the help

 

[ESquared]

I think the closest you can do is

INSERT Table EXEC StoredProc

So at least you could create a temp table with the correct columns and insert the results of the other stored proc, then select * from the temp table.

Every other permutation I've tried fails, that is, the following don't work:

SELECT * FROM EXEC StoredProc
SELECT * FROM (EXEC StoredProc) A

-------------------------------------
Only the unvirtuous can be dutiful, for the virtuous already perform duty's requirements by inclination and for pleasure. Where there is no pain, no disinclination, there is no duty.
- Erik E

 

[is47501]

I found the end run. A long way around but it gets you there...

SELECt *
FROM OPENROWSET('SQLOLEDB','server';'id';'password',
'EXEC database.dbo.sp @parms') AS a

 

[jbenson001]

hey is... great find... seems silly to have to do it that way.. but that is Microsoft for you... At least now I know it can be done

 

[ESquared]

Nice. Thanks for posting.