ユーザー定義関数の作成 (データベースエンジン)

2025-07-24

適用対象:SQL Server Azure SQL データベース 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 ステートメントは、ユーザー定義関数 (たとえば、 SET NOCOUNT ON;) では使用できません。変数値の代入では、 SETを使用できます。

FOR XML 句は使用できません。

入れ子になったユーザー定義関数

ユーザー定義関数は入れ子にすることができます。つまり、1 つのユーザー定義関数が別の関数を呼び出すことができます。呼び出された関数が実行を開始すると入れ子レベルが 1 つ上がり、呼び出された関数が実行を終了するとレベルが 1 つ下がります。

ユーザー定義関数は、32 レベルまで入れ子にすることができます。入れ子レベルが最大値を超えると、関数チェーン全体の呼び出しが失敗します。 Transact-SQL ユーザー定義関数からマネージドコードへの参照は、32 レベルの入れ子制限のうちの 1 レベルとカウントします。

マネージドコード内から呼び出されたメソッドは、この制限としてはカウントされません。

Service Broker ステートメント

以下の Service Broker ステートメントは、Transact-SQL ユーザー定義関数の定義に 含めることができません 。

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

副作用関数

次の非決定的な組み込み関数は、 Transact-SQL ユーザー定義関数 (UDF) では使用できません。

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

UDF 内でこれらの関数のいずれかを参照すると、次のエラーが発生します。

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

決定論的および非決定論的な組み込みシステム関数の一覧については、決定論的関数と非決定論的関数を参照してください。

この問題を回避するには、副作用関数をビューでラップし、関数内からビューを呼び出します。

アクセス許可

データベースの 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 AS INT;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory AS 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;

スカラー関数の詳細および例については、関数を作成するを参照してください。

テーブル値関数の例

インラインテーブル値関数 (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
          INNER JOIN Sales.SalesOrderDetail AS SD
              ON SD.ProductID = P.ProductID
          INNER JOIN Sales.SalesOrderHeader AS SH
              ON SH.SalesOrderID = SD.SalesOrderID
          INNER 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 INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
               e.OrganizationNode,
               p.FirstName,
               p.LastName,
               e.JobTitle,
               0
        FROM HumanResources.Employee AS e
             INNER JOIN Person.Person AS 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 AS e
             INNER JOIN EMP_cte
                 ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
             INNER JOIN Person.Person AS 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) の詳細および例については、関数を作成するを参照してください。

ベストプラクティス

ユーザー定義関数 (UDF) の作成時に SCHEMABINDING 句を使用しないと、基になるオブジェクトに加えられた変更が関数の定義に影響して、関数が呼び出されたときに予期しない結果が生じる可能性があります。基になるオブジェクトに対する変更によって関数が古くならないように、次のいずれかの操作を行うことをお勧めします。

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 ステートメントが成功します。

次の方法で共有

ユーザー定義関数の作成 (データベース エンジン)

制限事項

入れ子になったユーザー定義関数

Service Broker ステートメント

副作用関数

アクセス許可

スカラー関数の例

スカラー関数 (スカラー UDF)

テーブル値関数の例

インライン テーブル値関数 (TVF)

複数ステートメント テーブル値関数 (MSTVF)

ベスト プラクティス

関連するコンテンツ

フィードバック

その他のリソース

ユーザー定義関数の作成 (データベースエンジン)

インラインテーブル値関数 (TVF)

複数ステートメントテーブル値関数 (MSTVF)

ベストプラクティス