I need Execute Procedure return one value in select
select ImployeeId , ItemID , exec SP @ImployeeID = ImployeeID , @ItemID = ItemID
from table
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.
Good golly, NO! 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.
What does this stored procedure actually do? The best thing would be to rewrite the stored procedure as an "iTVF" (inline Table Valued Function).