A stored procedure with a single
@ProductIDparameter would allow us to add
WHERE ProductID = @ProductIDto both derived tables, which would make the query really fast. In my testing, it ran in under 100ms.
The problem is that this would require numerous code changes to the existing system. Many of the queries that rely on the view also use additional
WHEREconditions, meaning that a stored procedure is impractical.
Enter the table-valued user-defined function, or TVF.
I’m glad that the TVF worked out for him, but personally, I’m not a big fan of functions. In this case, though, it does the trick and leaves everyone happy.