CREATE FUNCTION (Transact-SQL)

[アーティクル]
12/16/2008

更新 : 2006 年 4 月 14 日

ユーザー定義関数を作成します。これは、値を返す、保存された Transact-SQL または共通言語ランタイム (CLR) のルーチンです。ユーザー定義関数は、データベースの状態を変更するアクションの実行に使用することはできません。ユーザー定義関数は、システム関数と同じように、クエリから起動できます。スカラ関数は、ストアドプロシージャと同様に、EXECUTE ステートメントを使用して実行できます。

ユーザー定義関数を変更するには ALTER FUNCTION を使用し、削除するには DROP FUNCTION を使用します。

トピックリンクアイコン Transact-SQL 構文表記規則

構文

Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]

Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

Function Options
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions
<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
        [ <table_constraint> ] [ ,...n ]
) 

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}
<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
            ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

引数

schema_name
ユーザー定義関数が属しているスキーマの名前です。

function_name
ユーザー定義関数の名前です。関数名は、識別子のルールに従っている必要があります。また、データベース内、およびそのスキーマに対して一意である必要があります。

メモ :

パラメータを指定しない場合でも、関数名の後にはかっこが必要です。

メモ :
パラメータを指定しない場合でも、関数名の後にはかっこが必要です。

**@**parameter_name
ユーザー定義関数内のパラメータです。1 つ以上のパラメータを宣言できます。

1 つの関数では、最高 1,024 個のパラメータを使用できます。宜言した各パラメータの値は、関数の実行時に、ユーザーが指定する必要があります (そのパラメータの既定値が定義されていない場合)。

最初の文字をアットマーク (@) にしてパラメータ名を指定します。パラメータ名は識別子のルールに従っている必要があります。パラメータは関数に対してローカルです。同じパラメータ名を他の関数で使用できます。パラメータは定数の代わりとしてのみ使用できます。パラメータは、テーブル名、列名、またはその他のデータベースオブジェクト名の代わりに使うことはできません。

メモ :
ストアドプロシージャまたはユーザー定義関数でパラメータを渡すとき、あるいはバッチステートメントで変数を宣言して設定するときには、ANSI_WARNINGS が無視されます。たとえば、変数が char(3) として定義されている場合に、3 文字より長い値に設定すると、定義されたサイズにデータが切り捨てられ、INSERT または UPDATE ステートメントが成功します。

ストアドプロシージャまたはユーザー定義関数でパラメータを渡すとき、あるいはバッチステートメントで変数を宣言して設定するときには、ANSI_WARNINGS が無視されます。たとえば、変数が char(3) として定義されている場合に、3 文字より長い値に設定すると、定義されたサイズにデータが切り捨てられ、INSERT または UPDATE ステートメントが成功します。

[ type_schema_name**.** ] parameter_data_type
パラメータのデータ型です。このデータ型が属するスキーマを指定することもできます。Transact-SQL 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、および timestamp を除くすべてのデータ型を指定できます。非スカラ型の cursor および table は、Transact-SQL 関数と CLR 関数のいずれでもパラメータのデータ型として指定できません。

type_schema_name を指定しない場合、SQL Server 2005 データベースエンジンは次の順序で scalar_parameter_data_type を検索します。
- SQL Server システムデータ型の名前を含むスキーマ
- 現在のデータベースの現在のユーザーの既定のスキーマ
- 現在のデータベースの dbo スキーマ

[ **=**default ]
パラメータの既定値です。default 値が定義されている場合は、パラメータに値を指定せずに関数を実行できます。

メモ :

varchar(max) データ型および varbinary(max) データ型の場合を除いて、CLR 関数には既定のパラメータ値を指定できます。

関数のパラメータに既定値がある場合に、既定値を取得する目的でその関数を呼び出すときは、DEFAULT キーワードを指定する必要があります。この動作は、ストアドプロシージャで既定値を持つパラメータを使用する場合とは異なります。ストアドプロシージャの場合は、パラメータを省略すると既定値が暗黙的に使用されます。

メモ :
varchar(max) データ型および varbinary(max) データ型の場合を除いて、CLR 関数には既定のパラメータ値を指定できます。

return_data_type
スカラユーザー定義関数の戻り値です。Transact-SQL 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、および timestamp を除くすべてのデータ型を指定できます。非スカラ型の cursor および table は、Transact-SQL 関数と CLR 関数の両方で戻り値のデータ型として指定できません。

function_body
総合して副作用 (テーブルの変更など) がない一連の Transact-SQL ステートメントが、関数の値を定義することを指定します。function_body は、スカラ関数と複数ステートメントのテーブル値関数でのみ使用されます。

スカラ関数の function_body は、総合してスカラ値と評価される一連の Transact-SQL ステートメントです。

複数ステートメントのテーブル値関数の function_body は、TABLE 戻り変数にデータを格納する一連の Transact-SQL ステートメントです。

scalar_expression
スカラ関数が返すスカラ値を指定します。

TABLE
テーブル値関数の戻り値がテーブルになるように指定します。テーブル値関数に渡すことができるのは、定数および **@**local_variables だけです。

インラインテーブル値関数の TABLE 戻り値は、単一の SELECT ステートメントを使用して定義します。インライン関数には、関連付けられている戻り変数はありません。

複数ステートメントのテーブル値関数の **@return_variable は TABLE 変数で、その関数の値として返される行の格納および蓄積に使用されます。@**return_variable は Transact-SQL 関数にのみ指定でき、CLR 関数には指定できません。

select_stmt
インラインテーブル値関数の戻り値を定義する単一の SELECT ステートメントです。

EXTERNAL NAME <method_specifier>, assembly_name.class_name.method_name
関数でバインドするアセンブリのメソッドを指定します。assembly_name は、表示がオンになっている現在のデータベースに存在する SQL Server のアセンブリと一致している必要があります。class_name は、有効な SQL Server 識別子であること、およびアセンブリにクラスとして存在していることが必要です。このクラスの名前が名前空間で修飾されており、名前空間の部分がピリオド (.) で分けられている場合は、このクラス名を角かっこ ([]) または引用符 ("") で区切る必要があります。method_name は、有効な SQL Server 識別子であること、および指定されたクラスの静的メソッドとして存在していることが必要です。

メモ :
既定では、SQL Server は CLR コードを実行できません。共通言語ランタイムモジュールを参照するデータベースオブジェクトを作成、変更、および削除することは可能ですが、SQL Server でこれらの参照を実行するには、clr enabled オプションを有効にする必要があります。このオプションを有効にするには、sp_configure を使用します。

既定では、SQL Server は CLR コードを実行できません。共通言語ランタイムモジュールを参照するデータベースオブジェクトを作成、変更、および削除することは可能ですが、SQL Server でこれらの参照を実行するには、clr enabled オプションを有効にする必要があります。このオプションを有効にするには、sp_configure を使用します。

<table_type_definition>, ( { <column_definition> <column_constraint> , | <computed_column_definition> } , [ <table_constraint> ] [ ,...n ], ) ,
Transact-SQL 関数のテーブルデータ型を定義します。テーブルの定義には、列の定義、および列またはテーブルの制約が含まれます。テーブルは、常にプライマリファイルグループに保存されます。

< clr_table_type_definition > , ( { column_namedata_type } [ ,...n ] ),
CLR 関数のテーブルデータ型を定義します。テーブルの定義には、列名およびデータ型のみが含まれます。テーブルは、常にプライマリファイルグループに保存されます。

<function_option>::= and <clr_function_option>::=

関数に以下のオプションを 1 つ以上指定します。

ENCRYPTION
データベースエンジンで、CREATE FUNCTION ステートメントの元のテキストを、暗号化した形式に変換することを示します。暗号化した形式の出力は、SQL Server 2005 内のどのカタログビューでも直接見ることはできません。システムテーブルまたはデータベースファイルへのアクセス権を持たないユーザーは、暗号化した形式のテキストを取得できません。DAC ポート経由でシステムテーブルにアクセスする権限、または直接データベースファイルにアクセスする権限を持っているユーザーは、このテキストを使用できます。また、サーバープロセスにデバッガをアタッチできるユーザーは、実行時、元のプロシージャをメモリから取得できます。システムメタデータへのアクセスの詳細については、「メタデータ表示の構成」を参照してください。

このオプションを使用すると、その関数を SQL Server レプリケーションの一部としてパブリッシュできなくなります。CLR 関数にはこのオプションを指定できません。

SCHEMABINDING
参照するデータベースオブジェクトに対して、その関数がバインドするように指定します。このバインドによって、他のスキーマバインドオブジェクトがその関数を参照している場合に関数が変更されるのを防ぐことができます。

関数からその参照先のオブジェクトへのバインドは、次のいずれかの操作が行われた場合にのみ削除されます。
- 関数を削除した場合。
- 関数を、SCHEMABINDING オプションを指定せずに ALTER ステートメントを使用して変更した場合。
関数をスキーマにバインドできるのは、次の条件が満たされている場合に限られます。
- 関数が Transact-SQL 関数である。
- 関数が参照するユーザー定義関数とビューも同様にスキーマにバインドされている。
- 関数が参照するオブジェクトが、2 つの要素から成る名前を使って参照されている。
- 関数およびその関数が参照するオブジェクトが、同じデータベースに属している。
- CREATE FUNCTION ステートメントを実行したユーザーが、その関数が参照するデータベースオブジェクトに対する REFERENCES 権限を持っている。
CLR 関数または別名データ型を参照する関数には、SCHEMABINDING を指定できません。

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
スカラ値関数の OnNULLCall 属性を指定します。指定しない場合は、既定で CALLED ON NULL INPUT が暗黙的に使用されます。つまり、NULL が引数として渡された場合でも、関数本体が実行されます。

CLR 関数で RETURNS NULL ON NULL INPUT が指定されていると、SQL Server は、受け取った引数のいずれかが NULL であった場合に関数の本体を呼び出すことなく NULL を返すことができます。<method_specifier> に指定された CLR 関数のメソッドに RETURNS NULL ON NULL INPUT を示すカスタム属性が既に設定されている場合に、CREATE FUNCTION ステートメントで CALLED ON NULL INPUT を指定すると、CREATE FUNCTION ステートメントの指定が優先されます。CLR テーブル値関数には、OnNULLCall 属性を指定できません。

EXECUTE AS 句
ユーザー定義関数が実行されるセキュリティコンテキストを指定します。つまり、SQL Server が、関数で参照されているデータベースオブジェクトに対する権限を検証する際に使用するユーザーアカウントを制御できます。

メモ :

インラインユーザー定義関数には EXECUTE AS を指定できません。

詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。

メモ :
インラインユーザー定義関数には EXECUTE AS を指定できません。

< column_definition >::=

Table 型を定義します。テーブルの定義には、列の定義および制約が含まれます。CLR 関数の場合は、column_name および data_type のみを指定できます。

column_name
テーブルの列名です。列名は、識別子のルールに従っていること、およびテーブル内で一意であることが必要です。column_name は 1 ～ 128 文字で指定できます。

data_type
列のデータ型を指定します。Transact-SQL 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、char、varchar、varchar(max)、および timestamp を除くすべてのデータ型を指定できます。非スカラ型の cursor は、Transact-SQL 関数と CLR 関数の両方で、列のデータ型として指定できません。

DEFAULT constant_expression
挿入の際に明示的な値を指定しない場合に、列に入力される値を指定します。constant_expression は、定数、NULL、またはシステム関数値です。DEFAULT 定義は、IDENTITY プロパティを持つ列を除くすべての列に適用できます。CLR テーブル値関数には DEFAULT を指定できません。

COLLATE collation_name
列の照合順序を指定します。照合順序を指定しない場合、データベースの既定の照合順序が列に割り当てられます。照合順序名には、Windows 照合順序名または SQL 照合順序名を指定できます。照合順序の一覧と詳細については、「Windows 照合順序名 (Transact-SQL)」および「SQL 照合順序名 (Transact-SQL)」を参照してください。

COLLATE 句を使用して照合順序を変更できるのは、char 型、varchar 型、nchar 型、および nvarchar 型の列だけです。

CLR テーブル値関数には COLLATE を指定できません。

ROWGUIDCOL
新しい列が行グローバル一意識別子列であることを示します。1 つのテーブルにつき、1 つの uniqueidentifier 列だけを ROWGUIDCOL 列に指定できます。ROWGUIDCOL プロパティは uniqueidentifier 列にだけ割り当てることができます。

ROWGUIDCOL プロパティは、列に格納されている値の一意性を設定しません。また、ROWGUIDCOL プロパティは、テーブルに挿入される新しい行の値を自動的に生成しません。各列に対して一意な値を生成するには、INSERT ステートメントで NEWID 関数を使用します。既定値も指定できますが、NEWID を既定値として指定することはできません。

IDENTITY
新しい列が ID 列であることを示します。テーブルに行が新しく追加されると、SQL Server は列に一意な増加値を設定します。ID 列は通常、PRIMARY KEY 制約と共に使用され、テーブルの一意な行識別子の役割を果たします。IDENTITY プロパティは、tinyint 型、smallint 型、int 型、bigint 型、decimal(p,0) 型、または numeric(p,0) 型の列に割り当てることができます。ID 列は、1 つのテーブルにつき、1 つだけ作成できます。バインドされた既定値および DEFAULT 制約を ID 列と組み合わせて使用することはできません。seed と increment は、両方を指定するか、どちらも指定しないでください。どちらも指定しないときの既定値は (1,1) です。

CLR テーブル値関数には IDENTITY を指定できません。
- seed
  テーブル内の先頭行に割り当てる整数値です。
- increment
  テーブル内の連続する行に対して、seed の値に加える整数値です。

< column_constraint >::= and < table_constraint>::=

指定された列またはテーブルの制約を定義します。CLR 関数の場合、制約の種類として指定できるのは NULL だけです。名前付き制約は使用できません。

NULL | NOT NULL
列で NULL 値を許すかどうかを示します。NULL は厳密には制約ではありませんが、NOT NULL と同じように指定することができます。CLR テーブル値関数には NOT NULL を指定できません。

PRIMARY KEY
一意なインデックスによって、指定した列に実体の整合性を設定する制約です。テーブル値ユーザー定義関数では、1 つのテーブルにつき 1 つの列にのみ PRIMARY KEY 制約を作成できます。CLR テーブル値関数には PRIMARY KEY を指定できません。

UNIQUE
一意なインデックスによって、指定した 1 つ以上の列に実体の整合性を持たせる制約です。1 つのテーブルは複数の UNIQUE 制約を持つことができます。CLR テーブル値関数には UNIQUE を指定できません。

CLUSTERED | NONCLUSTERED
PRIMARY KEY 制約または UNIQUE 制約に対して、クラスタ化インデックスまたは非クラスタ化インデックスを作成することを示します。PRIMARY KEY 制約では CLUSTERED が、UNIQUE 制約では NONCLUSTERED が、それぞれ使用されます。

CLUSTERED は 1 つの制約にのみ指定できます。UNIQUE 制約で CLUSTERED が指定され、PRIMARY KEY 制約も指定した場合には、PRIMARY KEY は NONCLUSTERED を使用します。

CLR テーブル値関数には、CLUSTERED および NONCLUSTERED を指定できません。

CHECK
1 つ以上の列に入力できる値を制限することによってドメインの整合性を設定する制約です。CLR テーブル値関数には CHECK 制約を指定できません。
- logical_expression
  TRUE または FALSE を返す論理式です。

<computed_column_definition>::=

計算列を指定します。計算列の詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。

column_name
計算列の名前です。

computed_column_expression
計算列の値を定義する式です。

<index_option>::=

PRIMARY KEY インデックスまたは UNIQUE インデックスのインデックスオプションを指定します。インデックスオプションの詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

PAD_INDEX = { ON | OFF }
インデックスの埋め込みを指定します。既定値は OFF です。

FILLFACTOR = fillfactor
インデックスの作成時または変更時に、データベースエンジンで各インデックスページのリーフレベルをどの程度まで使用するかを示す割合を指定します。fillfactor には、1 ～ 100 の整数値を指定する必要があります。既定値は 0 です。

IGNORE_DUP_KEY = { ON | OFF }
一意のクラスタ化インデックスまたは非クラスタ化インデックスに対する複数行の INSERT トランザクションでキー値に重複があった場合のエラー応答を指定します。既定値は OFF です。

STATISTICS_NORECOMPUTE = { ON | OFF }
分布統計を再計算するかどうかを指定します。既定値は OFF です。

ALLOW_ROW_LOCKS = { ON | OFF }
行ロックを許可するかどうかを指定します。既定値は ON です。

ALLOW_PAGE_LOCKS = { ON | OFF }
ページロックを許可するかどうかを指定します。既定値は ON です。

解説

ユーザー定義関数は、スカラ値関数とテーブル値関数のいずれかです。RETURNS 句でスカラ型のいずれかを指定した場合、関数はスカラ値関数になります。スカラ値関数は、複数の Transact-SQL ステートメントを使用して定義できます。

RETURNS 句で TABLE を指定した場合、関数はテーブル値関数になります。テーブル値関数は、関数の本文の定義方法に応じて、インライン関数と複数ステートメントの関数のいずれかに分類できます。詳細については、「ユーザー定義テーブル値関数」を参照してください。

関数で有効なステートメントは以下のとおりです。

代入ステートメント。
TRY...CATCH ステートメント以外の流れ制御ステートメント。
ローカルデータ変数およびローカルカーソルを定義する DECLARE ステートメント。
ローカル変数に値を代入する式を持つ選択リストが含まれている SELECT ステートメント。
関数内で宣言、オープン、クローズ、割り当ての解除を実行するローカルカーソルを参照するカーソル操作。INTO 句を使用してローカル変数に値を代入する FETCH ステートメントのみが許可され、クライアントにデータを返す FETCH ステートメントは許可されません。
ローカルな table 変数を変更する、INSERT、UPDATE、および DELETE ステートメント。
拡張ストアドプロシージャを呼び出す EXECUTE ステートメント。
詳細については、「ユーザー定義関数の作成 (データベースエンジン)」を参照してください。

ユーザー定義関数を入れ子にする

ユーザー定義関数は入れ子にすることができます。つまり、1 つのユーザー定義関数で、別のユーザー定義関数を呼び出すことができます。呼び出された関数が実行を開始すると入れ子レベルが 1 つ上がり、呼び出された関数が実行を終了するとレベルが 1 つ下がります。ユーザー定義関数は、32 レベルまで入れ子にすることができます。入れ子レベルが最大値を超えると、関数チェーン全体の呼び出しが失敗します。

メモ :
Transact-SQL ユーザー定義関数からマネージコードへの参照は、32 レベルの入れ子制限のうちの 1 レベルとカウントします。マネージコード内から呼び出されたメソッドは、この制限のうちにカウントしません。

関数のプロパティ

SQL Server の以前のバージョンでは、関数が決定的か非決定的かのみで分類されていました。SQL Server 2005 では、関数には以下のプロパティがあります。これらのプロパティの値によって、保存される計算列またはインデックス付き計算列で関数を使用できるかどうかが決まります。

プロパティ	説明	注
IsDeterministic	関数が決定的か非決定的かを示します。	決定的関数では、ローカルデータアクセスが可能です。たとえば、特定の一連の入力値を使用して同じ状態のデータベースで呼び出されるたびに、必ず同じ結果を返す関数は、決定的と呼ばれます。
IsPrecise	関数が正確か不正確かを示します。	不正確な関数には、浮動小数点演算などの演算を含みます。
IsSystemVerified	関数の有効桁数のプロパティと決定性のプロパティは、SQL Server で確認できます。
SystemDataAccess	関数が、SQL Server のローカルインスタンスのシステムデータ (システムカタログまたは仮想システムテーブル) にアクセスします。
UserDataAccess	関数が、SQL Server のローカルインスタンスのユーザーデータにアクセスします。	ユーザー定義テーブルと一時テーブルが含まれますが、テーブル変数は含まれません。

Transact-SQL 関数の有効桁数のプロパティと決定性のプロパティは、SQL Server によって自動的に決定されます。詳細については、「ユーザー定義関数のデザインガイドライン」を参照してください。CLR 関数のデータアクセスプロパティと決定性のプロパティは、ユーザーが指定できます。詳細については、「CLR 統合のカスタム属性の概要」を参照してください。

これらのプロパティの現在の値を表示するには、OBJECTPROPERTYEX を使用します。

ユーザー定義関数を呼び出す計算列のインデックス付け

ユーザー定義関数に以下のプロパティ値がある場合、その関数を呼び出す計算列をインデックスで使用できます。

IsDeterministic = true
IsSystemVerified = true (保存される計算列である場合以外)
UserDataAccess = false
SystemDataAccess = false

詳細については、「計算列に対するインデックスの作成」を参照してください。

関数からの拡張ストアドプロシージャ呼び出し

拡張ストアドプロシージャは、関数の内部から呼び出された場合、結果セットをクライアントに返しません。結果セットをクライアントに返す ODS API はすべて FAIL を返します。拡張ストアドプロシージャは SQL Server のインスタンスに接続し直すことはできますが、その拡張ストアドプロシージャを起動した関数と同じトランザクションに参加することはできません。

バッチやストアドプロシージャから起動される場合と同じように、拡張ストアドプロシージャは、SQL Server を実行している Windows セキュリティアカウントのコンテキストで実行されます。ストアドプロシージャの所有者は、その EXECUTE 権限をユーザーに与えるときに、この点を考慮する必要があります。

関数の呼び出し

スカラ値関数は、スカラ式が使用されている場所で呼び出すことができます。これには、計算列および CHECK 制約定義が含まれます。スカラ値関数は、EXECUTE ステートメントを使用して実行することもできます。スカラ値関数は、少なくとも 2 つの要素で構成される名前の関数を使用して呼び出す必要があります。複数の要素で構成される名前の詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。SELECT、INSERT、UPDATE、DELETE の各ステートメントの FROM 句でテーブル式を使用できる場合は、テーブル値関数を呼び出すことができます。詳細については、「ユーザー定義関数の実行 (データベースエンジン)」を参照してください。

CLR 関数でのパラメータおよび戻り値の使用

CLR 関数で指定するパラメータは、以前 scalar_parameter_data_type 用に定義されていた SQL Server 型にしてください。SQL Server のシステムデータ型と CLR 統合データ型または .NET Framework 共通言語ランタイムデータ型との比較については、SQL Server データ型と .NET Framework データ型の対応を参照してください。

SQL Server で、クラスにオーバーロードされているメソッドを正しく参照するには、<method_specifier> で指定するメソッドに以下の特性が必要です。

[ ,...n ] での指定と同数のパラメータを受け取る。 ].
すべてのパラメータを、参照ではなく値で受け取る。
SQL Server 関数で指定されたパラメータ型と互換性のあるパラメータ型を使用する。

CLR 関数から返されるデータ型がテーブル型 (RETURNS TABLE) である場合、<method_specifier> のメソッドから返されるデータ型は、IEnumerator または IEnumerable である必要があります。また、関数の作成者によってインターフェイスが実装されるものと見なされます。Transact-SQL 関数とは異なり、CLR 関数では、<table_type_definition> に PRIMARY KEY、UNIQUE、または CHECK 制約を含めることができません。<table_type_definition> に指定する列のデータ型は、<method_specifier> のメソッドの実行時に返される結果セット内の、対応する列の型に一致する必要があります。この型チェックは、関数の作成時には実行されません。

CLR 関数プログラミングの詳細については、「CLR ユーザー定義関数」を参照してください。

禁止される SQL ステートメント

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

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

関数に関する情報の表示

Transact-SQL ユーザー定義関数の定義を表示するには、関数の存在するデータベースで sys.sql_modules カタログビューを使用します。

次に例を示します。

USE AdventureWorks;
GO
SELECT definition, type 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND type IN ('FN', 'IF', 'TF');
GO

メモ :
ENCRYPTION オプションを使用して作成した関数の定義は、sys.sql_modules で表示することができません。ただし、暗号化された関数に関するその他の情報は表示されます。

CLR ユーザー定義関数に関する情報を表示するには、関数の存在するデータベースで sys.assembly_modules カタログビューを使用します。

ユーザー定義関数で定義されたパラメータに関する情報を表示するには、関数の存在するデータベースで sys.parameters カタログビューを使用します。

関数が参照するオブジェクトに関するレポートを表示するには、sys.sql_dependencies を使用します。

権限

データベースの CREATE FUNCTION 権限と、関数を作成するスキーマの ALTER 権限が必要です。関数でユーザー定義型が指定されている場合は、その型に対する EXECUTE 権限が必要です。

例

A. ISO 週番号を計算するスカラ値ユーザー定義関数を使用する

次の例では、ユーザー定義関数 ISOweek を作成します。この関数は、日付引数を受け取って、ISO 週番号を計算します。この関数が正しい計算を行うためには、関数を呼び出す前に SET DATEFIRST 1 を呼び出す必要があります。

また、この例では、EXECUTE AS 句を使用して、ストアドプロシージャを実行できるセキュリティコンテキストを指定します。この例のオプション CALLER は、プロシージャが呼び出し元ユーザーのコンテキストで実行されることを指定しています。指定可能な他のオプションは、SELF、OWNER、および user_name です。

以下に関数呼び出しを示します。DATEFIRST が 1 に設定されていることに注意してください。

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int;
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1;
     RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

以下に結果セットを示します。

ISO Week
----------------
52

B. インラインテーブル値関数を作成する

次の例では、インラインテーブル値関数を返します。ここでは、店舗に販売された各製品ごとに 3 つの列を返します。ProductID、Name、および YTD Total (今年に入ってからの店舗別合計の集計) です。

USE AdventureWorks;
GO
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 'YTD 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
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

関数を呼び出すには、次のクエリを実行します。

SELECT * FROM Sales.ufn_SalesByStore (602);

C. 複数ステートメントのテーブル値関数を作成する

次の例では、テーブル値関数 fn_FindReports(InEmpID) を作成します。有効な従業員 ID をこの関数に指定すると、その従業員に対して直接または間接の報告関係にあるすべての従業員に対応した表が返されます。この関数は、再帰共通テーブル式 (CTE) を使用して、従業員の階層リストを生成します。再帰 CTE の詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください。

USE AdventureWorks;
GO
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,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO

D. CLR 関数を作成する

次の例では、SQL Server データベースエンジンのサンプルがローカルコンピュータの既定の位置にインストールされていて、StringManipulate.csproj サンプルアプリケーションがコンパイルされていることを前提としています。詳細については、「補助文字対応文字列操作」を参照してください。

この例では、CLR 関数 len_s を作成します。関数が作成される前に、アセンブリ SurrogateStringFunction.dll がローカルデータベースに登録されます。

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

CLR テーブル値関数の作成例については、CLR テーブル値関数を参照してください。

参照

その他の技術情報

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

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2006 年 4 月 14 日	新しい内容 : 「解説」の「関数の呼び出し」に、スカラ値関数を呼び出すには少なくとも 2 つの要素で構成される名前の関数を使用する必要があることを追記。
2005 年 12 月 5 日	変更内容 ENCRYPTION オプションの定義を明記。

次の方法で共有

CREATE FUNCTION (Transact-SQL)

構文

引数

解説

ユーザー定義関数を入れ子にする

関数のプロパティ

ユーザー定義関数を呼び出す計算列のインデックス付け

関数からの拡張ストアドプロシージャ呼び出し

関数の呼び出し

CLR 関数でのパラメータおよび戻り値の使用

禁止される SQL ステートメント

関数に関する情報の表示

権限

例

A. ISO 週番号を計算するスカラ値ユーザー定義関数を使用する

B. インラインテーブル値関数を作成する

C. 複数ステートメントのテーブル値関数を作成する

D. CLR 関数を作成する

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

次の方法で共有

CREATE FUNCTION (Transact-SQL)

構文

引数

解説

ユーザー定義関数を入れ子にする

関数のプロパティ

ユーザー定義関数を呼び出す計算列のインデックス付け

関数からの拡張ストアド プロシージャ呼び出し

関数の呼び出し

CLR 関数でのパラメータおよび戻り値の使用

禁止される SQL ステートメント

関数に関する情報の表示

権限

例

A. ISO 週番号を計算するスカラ値ユーザー定義関数を使用する

B. インライン テーブル値関数を作成する

C. 複数ステートメントのテーブル値関数を作成する

D. CLR 関数を作成する

参照

関連項目

その他の技術情報

ヘルプおよび情報

変更履歴

その他のリソース

関数からの拡張ストアドプロシージャ呼び出し

B. インラインテーブル値関数を作成する