Tabelaryczne UDF

Funkcje i procedury składowane w SQL Server

Meghan Kwartler

IT Consultant

Wbudowane funkcje tabelaryczne (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;

Różnice – ITVF a MSTVF

Wbudowana

RETURN zwraca wynik SELECT
Nazwy kolumn w SELECT
Brak zmiennej tabelarycznej
Nie wymaga BEGIN END
Brak INSERT
Wyższa wydajność

Wieloinstrukcyjna

Deklaracja zmiennej tabelarycznej do zwrócenia
Wymagany blok BEGIN END
INSERT danych do zmiennej tabelarycznej
RETURN jako ostatnia instrukcja w bloku BEGIN/END

Czas na ćwiczenia!

Funkcje i procedury składowane w SQL Server