Galin Iliev's blog

Stored procedures are very powerful and they have many benefits than using UDF (user defined function).

There is one cons though - resultset cannot be manipulated further from T-SQL.

There is one trick that will allow it: by using OPENROWSET:

SELECT  * 
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS tbl

Read full blog post here