表值 UDF

SQL Server 中的函数与存储过程编写

Meghan Kwartler

IT Consultant

内联表值函数（ITVF）

CREATE FUNCTION SumLocationStats (
  @StartDate AS datetime = '1/1/2017'
) RETURNS TABLE AS RETURN 
SELECT 
  PULocationID AS PickupLocation, 
  COUNT(ID) AS RideCount, 
  SUM(TripDistance) AS TotalTripDistance 
FROM YellowTripData 
WHERE CAST(PickupDate AS Date) = @StartDate 
GROUP BY PULocationID;

CREATE FUNCTION CountTripAvgFareDay (
  @Month char(2), 
  @Year char(4)
) RETURNS @TripCountAvgFare TABLE(
  DropOffDate date, TripCount int, AvgFare numeric
) AS BEGIN INSERT INTO @TripCountAvgFare 
SELECT 
  CAST(DropOffDate as date), 
  COUNT(ID), 
  AVG(FareAmount) as AvgFareAmt 
FROM YellowTripData 
WHERE 
  DATEPART(month, DropOffDate) = @Month 
  AND DATEPART(year, DropOffDate) = @Year 
GROUP BY CAST(DropOffDate as date)
RETURN END;

差异——ITVF vs. MSTVF

【内联】

返回 SELECT 的结果
列名在 SELECT 中定义
无表变量
不需要 BEGIN/END
无 INSERT
性能更快

【多语句】

声明要返回的表变量
需要 BEGIN/END 块
将数据 INSERT 到表变量
RETURN 为 BEGIN/END 内最后一条语句

该你了！

SQL Server 中的函数与存储过程编写