Call a storedproc in select from block

Hi everyone,

I have a storedproc. This proc send back a value. how can i call this storedproc in select from block. Or what is your advise for other ways....

Select *

, (Exec MyStoredProc MyParam) as Field1

From Table1

It's complicated. You need to set up a (possible looped back) linked server and invoke it through the OPENROWSET() function. It is is explained here

http://www.sqlmag.com/Article/ArticleID/19842/sql_server_19842.html

You may need to enable the 'Ad Hoc Distributed Queries' sp_configure options in SQL 2005 (not sure ... I didn't try)

From a TSQL perspective, one of the main reasons we don't let you invoke procedures from inside queries is that we cannot determine the shape of the result set at the time we compile the query plan. Moreover, if the procedure has side effects, the semantics become unclear (e.g. resuls are different if you invoke once per row or once per query).

By using the "trick" above, the full distributed query machinery kicks into action which involves a lot of extra overhead not found in normal queries. Frankly, I'm not sure what the semantic is if you try something like

SELECT * FROM mytable JOIN OPENROWSET(..., 'EXEC myproc') ...

Perhaps you can try rewriting your code to use a table-valued function if it doesn't have side effects. This will give you better performance and predictable semantics.