In one of my recent articles, I mentioned the use of Table Valued Function (TVF) instead of Stored Procedure (SP). I received a follow up email asking what type of SP can be converted into a TVF. This is indeed a very interesting question! In fact, not all the SPs qualify to be converted to a TVF.
Please note that I am not encouraging to convert all the SPs to TVFs. Each SPs have their own usage and need. Here, I shall discuss about the type of SP that can be converted to a TVF.
First of all, you need to convert the SP that you are using to retrieve data and to insert it into any other table. Please read my original post wherein I have explained the scenario for the same. If you do not have to insert the data from the SP into another table, then there is no need to convert the SP to a TVF. This conversion will not necessarily enhance the performance.
In general, SP that returns only one resultset qualifies for conversion to TVF. If there are more than one resultset, then the SP cannot be converted to TVF as the TVF always returns one resultset. Moreover, if the SP has DML statements such as DELETE, UPDATE, INSERT or MERGE, it cannot be converted to a TVF. The usage of TVF is limited to the SELECT statement only. If the SP must be executed by using EXEC, it cannot be converted to TVF.
Do you know any other reason why a SP needs to be converted to a TVF?
Reference: Pinal Dave (http://blog.sqlauthority.com)
pinaldave
Well, this is indeed a good discussion. according to me, if we are having just one SELECT statement which is returning one result sets than it would be good to convert it into TVF because we can join that result set to other SELECT statement.
while using SP, you can’t join it with anything else directly.
Hi Pinal,
Can you provide a link to the original article about converting stored procedures to TMVs?
Sure,
I am reposting my original article in a day or so, as I found something new in regards to that article.
Kind Regards,
Pinal
how to insert data in two tables to use one Store Procedure
You said “Moreover, if the SP has DML statements such as DELETE, UPDATE, INSERT or MERGE, it cannot be converted to a TVF. The usage of TVF is limited to the SELECT statement only.”
That is true of an in-line TVF, but not true of a multi-statement TVF. If you could not INSERT into the table defined in the return of a multi-statement TVF there would not be anything to RETURN.
It would be correct if you added the exception stated in Books Online, “except to table variables”.