UDF の実践

SQL Server における関数とストアドプロシージャの作成

Meghan Kwartler

IT Consultant

SELECT でスカラーを実行

-- パラメータなしで選択
SELECT dbo.GetTomorrow()

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

EXEC でスカラーを実行し結果を保存

-- EXEC で実行し、変数に保存
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 でパラメータとスカラー UDF

-- パラメータ変数を宣言
-- YellowTripData の最古日付を設定
-- 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   |
+-------------+-----------+---------+

関数を動かして確認しましょう

SQL Server における関数とストアドプロシージャの作成