UDF dalam praktik

Menulis Function dan Stored Procedure di SQL Server

Meghan Kwartler

IT Consultant

Jalankan skalar dengan SELECT

-- Select tanpa parameter
SELECT dbo.GetTomorrow()

+------------+
| 2019-02-28 |
-------------+

Jalankan skalar dengan EXEC & simpan hasil

-- EXEC & simpan hasil ke variabel
DECLARE @TotalRideHrs AS numeric 
EXEC @TotalRideHrs = dbo.GetRideHrsOneDay @DateParm = '1/15/2017' 
SELECT 
  'Total Ride Hours for 1/15/2017:', 
  @TotalRideHrs

+---------------------------------+-------+
| Total Ride Hours for 1/15/2017: | 71626 | 
+---------------------------------+-------+

SELECT nilai parameter & UDF skalar

-- Deklarasikan variabel parameter
-- Setel ke tanggal tertua di YellowTripData
-- Teruskan ke fungsi dengan select
DECLARE @DateParm as date = 
(SELECT TOP 1 CONVERT(date, PickupDate) 
    FROM YellowTripData 
    ORDER BY PickupDate DESC)
SELECT @DateParm, dbo.GetRideHrsOneDay (@DateParm)

+------------+-------+
| 2017-01-31 | 75519 | 
+------------+-------+

SELECT TOP 10 * 
FROM dbo.SumLocationStats ('1/09/2017')
ORDER BY RideCount DESC

+----------------+-----------+-------------------+
| PickupLocation | RideCount | TotalTripDistance |
|----------------+-----------+-------------------|
| 237            |13254      |  22281.95         |
| 161            |13206      |  28208.49         |
| 236            |13200      |  24224.69         |
| 162            |11859      |  26169.46         |
| 186            |10587      |  22415.43         |
| 230            |10257      |  26139.16         |
| 234            |10234      |  19758.23         |
| 170            |9963       |  20931.97         |
| 132            |9230       | 144778.90         |
| 48             |8361       |  18978.80         |
+----------------+-----------+-------------------+

DECLARE @CountTripAvgFareDay TABLE(
     DropOffDate    date,
     TripCount      int,
     AvgFare        numeric)   
INSERT INTO @CountTripAvgFareDay
SELECT TOP 10 * 
FROM dbo.CountTripAvgFareDay (01, 2017)
ORDER BY DropOffDate ASC

SELECT * FROM @CountTripAvgFareDay

+-------------+-----------+---------+
| DropOffDate | TripCount | AvgFare |
|-------------+-----------+---------|
| 2017-01-01  |279198     | 15.37   |
| 2017-01-02  |225224     | 12.65   |
| 2017-01-03  |277980     | 12.27   |
| 2017-01-04  |289050     | 12.33   |
| 2017-01-05  |323885     | 11.89   |
| 2017-01-06  |339158     | 11.72   |
| 2017-01-07  |306508     | 11.31   |
| 2017-01-08  |292649     | 12.33   |
| 2017-01-09  |302120     | 12.49   |
| 2017-01-10  |305611     | 12.27   |
+-------------+-----------+---------+

Lihat fungsi Anda beraksi!

Menulis Function dan Stored Procedure di SQL Server