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:
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
Read full blog post here