Obuka
Modul
Create stored procedures and user-defined functions - Training
This content is a part of Create stored procedures and user-defined functions.
Ovaj pregledač više nije podržan.
Nadogradite na Microsoft Edge biste iskoristili najnovije funkcije, bezbednosne ispravke i tehničku podršku.
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.
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
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
Obuka
Modul
Create stored procedures and user-defined functions - Training
This content is a part of Create stored procedures and user-defined functions.