ユーザー定義関数を作成する (データベース エンジン)
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
この記事では、Transact-SQL を使って SQL Server でユーザー定義関数 (UDF) を作成する方法について説明します。
制限事項と制約事項
ユーザー定義関数は、データベースの状態を変更するアクションの実行に使うことはできません。
出力先がテーブルである
OUTPUT INTO句をユーザー定義関数に含めることはできません。ユーザー定義関数で複数の結果セットを返すことはできません。 複数の結果セットを返す必要がある場合は、ストアド プロシージャを使用します。
エラー処理は、ユーザー定義の関数では制限されます。 UDF では、
TRY...CATCH、@ERROR、またはRAISERRORはサポートされていません。ユーザー定義関数でストアド プロシージャを呼び出すことはできませんが、拡張ストアド プロシージャを呼び出すことはできます。
ユーザー定義関数では、動的 SQL または一時テーブルを利用することはできません。 テーブル変数は使用できます。
SETステートメントはユーザー定義関数では使用できません。FOR XML句は使用できません。ユーザー定義関数は入れ子にすることができます。つまり、1 つのユーザー定義関数で、別のユーザー定義関数を呼び出すことができます。 呼び出された関数が実行を開始すると入れ子レベルが 1 つ上がり、呼び出された関数が実行を終了するとレベルが 1 つ下がります。 ユーザー定義関数は、32 レベルまで入れ子にすることができます。 入れ子レベルが最大値を超えると、関数チェーン全体の呼び出しが失敗します。 Transact-SQL ユーザー定義関数からマネージド コードへの参照は、32 レベルの入れ子制限のうちの 1 レベルとカウントします。 マネージド コード内から呼び出されたメソッドは、この制限としてはカウントされません。
以下の Service Broker ステートメントは、Transact-SQL ユーザー定義関数の定義に 含めることができません 。
BEGIN DIALOG CONVERSATIONEND CONVERSATIONGET CONVERSATION GROUPMOVE CONVERSATIONRECEIVESEND
アクセス許可
データベースの CREATE FUNCTION 権限と、関数を作成するスキーマの ALTER 権限が必要です。 関数でユーザー定義型が指定されている場合は、その型に対する EXECUTE 権限が必要です。
スカラー関数の例
スカラー関数 (スカラー UDF)
次の例では、AdventureWorks2022 データベースで複数ステートメントの "スカラー関数 (スカラー UDF)" を作成します。 この関数は、1 つの入力値 ProductIDを受け取り、単一のデータ値 (在庫品目中の指定された製品に関する集計量) を返します。
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
次に、 ufnGetInventoryStock 関数を使用して、 ProductModelID が 75 ～ 80 の製品の現在の在庫量を返す例を示します。
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
スカラー関数の詳細と例については、「CREATE FUNCTION」を参照してください。
テーブル値関数の例
インライン テーブル値関数 (TVF)
次の例では、AdventureWorks2022 データベースでインライン テーブル値関数 (TVF) を作成します。 この関数は、入力パラメーターとして 1 つの顧客 (商店) ID を受け取り、 ProductID列と Name列、および過去 1 年間の集計である YTD Total を商店に販売した製品ごとに返します。
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
次の例では、この関数を呼び出して顧客 ID 602 を指定します。
SELECT * FROM Sales.ufn_SalesByStore (602);
複数ステートメントのテーブル値関数 (MSTVF)
次の例では、AdventureWorks2022 データベースで複数ステートメントのテーブル値関数 (MSTVF) を作成します。 この関数は、単一の入力パラメーター EmployeeID を受け取り、指定された従業員の直接または間接の部下であるすべての従業員の一覧を返します。 関数が呼び出され、従業員 ID 109 を指定します。
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte
RETURN
END;
GO
次の例では、この関数を呼び出して顧客 ID 1 を指定します。
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);
インライン テーブル値関数 (インライン TVF) と複数ステートメント テーブル値関数 (MSTVF) の詳細と例については、「CREATE FUNCTION」を参照してください。
ベスト プラクティス
SCHEMABINDING 句を使わないでユーザー定義関数 (UDF) を作成すると、基になるオブジェクトに対する変更が関数の定義に影響を与え、呼び出されたときに予期しない結果になる可能性があります。 基になるオブジェクトに対する変更のために関数が古くならないように、次のいずれかの方法を実装することをお勧めします。
UDF を作成するときに、
WITH SCHEMABINDING句を指定します。 このようにすると、関数定義で参照されているオブジェクトは、一緒に関数も変更しない限り変更できません。UDF の定義で指定されているオブジェクトを変更した後に sp_refreshsqlmodule ストアド プロシージャを実行します。
データにアクセスしない UDF を作成する場合は、SCHEMABINDING オプションを指定して、これらの UDF を含むクエリ プランに対してクエリ オプティマイザーが不要なスプール演算子を生成しないようにします。 スプールの詳細については、「プラン表示の論理操作と物理操作のリファレンス」を参照してください。 スキーマ バインド関数の作成の詳細については、「スキーマ バインド関数」を参照してください。
FROM 句内で MSTVF に結合することは可能ですが、その結果パフォーマンスが低下する可能性があります。 SQL Server では、MSTVF に含めることができる一部のステートメントに対して、すべての最適化技法を使用できるわけではないため、最適化されていないクエリ プランになる場合があります。 パフォーマンスをできるだけ高くするには、可能な限り、関数間ではなくベース テーブル間の結合を使用してください。
MSTVF の固定のカーディナリティの推定は、SQL Server 2014 (12.x) 以降のバージョンでは 100、それより前の SQL Server バージョンでは 1 となります。
SQL Server 2017 (14.x) 以降では、MSTVF を使用する実行プランの最適化でインターリーブ実行を使用でき、上記のヒューリスティックではなく実際のカーディナリティが使われるようになります。
詳細については、「複数ステートメントのテーブル値関数のインターリーブ実行」を参照してください。
ストアド プロシージャまたはユーザー定義関数でパラメーターを渡すとき、またはバッチ ステートメントで変数を宣言して設定するとき、ANSI_WARNINGS は適用されません。 たとえば、変数を char(3) と定義し、これに 4 文字以上の値を設定すると、データが定義されたサイズに合わせて切り捨てられてから、INSERT または UPDATE ステートメントが成功します。