a SELECT statement using exec

[stiej]

Hi,

Is it possible to execute another procedure in the "from" bit of a select statement as in:

select id from exec anothersp

to benefit clarity, anothersp would normally return, say, id, name, age or whatever.

Any help much appreciated!

Thanks.

 

[JayKusch]

The object after the FROM clause needs to be a table or view.

Thanks

J. Kusch

 

[checkai]

You could fill a temp table with the results of the sp and then select from that...

dlc

 

[stiej]

okay, thanks guys. did think of the temp table thing, but thought it'd be more nice and "inline" if you could get the results directly from exec another procedure there and then. Thanks though!

 

[gtr111]

If you are using sql2k - you could employ a user defined function that returns a table

 

[stiej]

that sounds interesting? Can you exapnd?

 

[gtr111]

stiej,
Just look up 'SQL User-Defined Functions' in books on line.

The bottom line is that you can create a function whose return value is a table - and you can use that return value just as if it were a table or view as in select * from dbo.myUdf(parm).

There were many, many improvements from version 7 to 2k, but I have to admit that user defined functions are my favorite.