Exec Procedure in select

Transact-SQL
1

I need Execute Procedure return one value in select
select ImployeeId , ItemID , exec SP @ImployeeID = ImployeeID , @ItemID = ItemID
from table

2

Not quite clear whether you are trying to get the return value of the stored procedure, or a column from the return dataset of the stored procedure.

If it is the latter, use OPENROWSET or EXEC..INSERT.

If it is the latter, I can't immediately think of a simple way of doing it other than through a loop.

3

Good golly, NO! :wink: That means that the stored procedure will have to execute once for each and every row in the table and it will likely be slower and more resource intensive than a Scalar Function or Multi-Statement Table Valued Function (mTVF, for short). If you do it this way, your next post will probably be asking how to improve the performance. :wink:

What does this stored procedure actually do? The best thing would be to rewrite the stored procedure as an "iTVF" (inline Table Valued Function).