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.
Some advantages are...
It can use stored procedures to populate it.
It can utilize temp tables where views cannot.
It can have parameters.
It will present the data definition of it's output to a calling program where a stored procedure will not.
It can be used in the FROM clause of a view if it doesn't have parameters.
Some disadvantages are...
Excel will not allow you to use it for a datasource.