Pročitaj na engleskom Uredi

Deli putem


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);
}

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 };

See also