Search This Blog

Monday, November 11, 2013

Inline Table Value Function V/S Views

ITVF can be parameterized but views are not. If you need a subset of rows from a view, you need to apply a search criteria in a WHERE clause, but an ITVF can accept search criteria as function parameters.
When quering two tables joined together, the implementation can use the parameter in the JOIN condition while using ITVF. But in views, you need to add it in the Where condition.

Suppose you need to get all titles written by authors whose last name is stored in variable @author. Here's the code using the view:

SELECT a.*, t.titleid
FROM viewauthors a
JOIN titleauthor t ON a.auid = t.auid
WHERE a.au_lname = @author

In case of ITVF, you can use it as;

SELECT a.*, t.titleid
FROM dbo.itvfauthors( @author ) a
JOIN titleauthor t ON a.auid = t.auid

As you can see, you do not need a WHERE condition and the reslt set iof ITVF is filtered via parameter @author.

No comments:

Post a Comment