Tom Garth (10/29/2009)
David McKinney (10/29/2009)
ruedifuchs (10/29/2009)
I agree, the code for using OPENROWSET is very interesting.But since the stored procedure has to return a result set, what is the advantage over using a table-valued function?
I'm not sure if I understand your question, ruedi, but here goes.
The examples given, sp_who, sp_lock, are SYSTEM stored procedures (i.e. not yours!) - they are not table-valued functions. The data returned by these is very useful - but difficult to use effectively (filter, join, sort etc) unless you can put it into a recordset of some sort (table / view / CTE etc). The OPENROWSET code enables you to do that. Short of rewriting the entire sp as a table-valued function, I'm not sure how a table-valued function can help you?
Does this clarify?
I think that "ruedifuchs" meant that a Table Valued Function does provide a recordset including the table definition and could probably replace the stored procedure and be called directly with a select statement.
Tom,
....could probably replace WHICH stored procedure? The sp_who / sp_lock? If this is what you mean, then I agree, sp_who, sp_lock would be handier if they were table type functions, BUT THEY ARE NOT! Microsoft didn't write them that way. Until they do (and as was pointed out DMV's may be an alternative), this (with some caveats) provides a fairly generic way of converting their output to a recordset. The alternative would be to rewrite ourselves sp_who / sp_lock as a table type function, but that's hardly a generic or quick solution.
Do you get my point .... or have I missed yours?
Regards,
David.