SQL函数返回结果集的两种写法
创建内联表值函数
下⾯的⽰例将返回内联表值函数。对于销售给商店的每个产品，该函数返回三列，分别为ProductID、Name以及各个商店年初⾄今总数的累计YTD Total。
USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
橙汁怎么榨
(
异文化
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid代办离职证明
GROUP BY P.ProductID, P.Name
);
GO
若要调⽤该函数，请运⾏此查询。
鸟的诗句
作文桃花SELECT * FROM Sales.ufn_SalesByStore (602);
C. 创建多语句表值函数
下⾯的⽰例创建表值函数fn_FindReports(InEmpID)。如果提供⼀个有效雇员 ID，该函数将返回⼀个表，该表对应于直接或间接向该雇员报告的所有雇员。该函数使⽤递归公⽤表表达式 (CTE) 来⽣成雇员的层次结构列表。有关递归 CTE 的详细信息，请参阅。
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE提前录取
(
EmployeeID int primary key NOT NULL,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result t that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
娜芙蒂蒂JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO腾讯企业文化
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO
