Table valued UDFs

Writing Functions and Stored Procedures in SQL Server

Meghan Kwartler

IT Consultant

Inline table valued functions (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;

Differences - ITVF vs. MSTVF

Inline

RETURN results of SELECT
Table column names in SELECT
No table variable
No BEGIN END needed
No INSERT
Faster performance

Multi statement

DECLARE table variable to be returned
BEGIN END block required
INSERT data into table variable
RETURN last statement within BEGIN/END block

Your turn!

Writing Functions and Stored Procedures in SQL Server