How to: Use Table-Valued User-Defined Functions
A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table
, you can use a table-valued function anywhere in SQL that you can use a table. You can also treat the table-valued function just as you would a table.
Example 1
The following SQL function explicitly states that it returns a TABLE
. Therefore, the returned rowset structure is implicitly defined.
CREATE FUNCTION ProductsCostingMoreThan(@cost money)
RETURNS TABLE
AS
RETURN
SELECT ProductID, UnitPrice
FROM Products
WHERE UnitPrice > @cost
LINQ to SQL maps the function as follows:
[Function(Name="dbo.ProductsCostingMoreThan", IsComposable=true)]
public IQueryable<ProductsCostingMoreThanResult> ProductsCostingMoreThan([Parameter(DbType="Money")] System.Nullable<decimal> cost)
{
return this.CreateMethodCallQuery<ProductsCostingMoreThanResult>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), cost);
}
<FunctionAttribute(Name:="dbo.ProductsCostingMoreThan", IsComposable:=True)> _
Public Function ProductsCostingMoreThan(<Parameter(DbType:="Money")> ByVal cost As System.Nullable(Of Decimal)) As IQueryable(Of ProductsCostingMoreThanResult)
Return Me.CreateMethodCallQuery(Of ProductsCostingMoreThanResult)(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), cost)
End Function
Example 2
The following SQL code shows that you can join to the table that the function returns and otherwise treat it as you would any other table:
SELECT p2.ProductName, p1.UnitPrice
FROM dbo.ProductsCostingMoreThan(80.50)
AS p1 INNER JOIN Products AS p2 ON p1.ProductID = p2.ProductID
In LINQ to SQL, the query would be rendered as follows:
var q =
from p in db.ProductsCostingMoreThan(80.50m)
join s in db.Products on p.ProductID equals s.ProductID
select new { p.ProductID, s.UnitPrice };
Dim q = _
From p In db.ProductsCostingMoreThan(80.5), p1 In db.Products _
Where p.ProductID = p1.ProductID _
Select p.ProductID, p1.UnitPrice