共用方式為

CREATE FUNCTION (Transact-SQL)

  • 發行項

更新： 2006 年 4 月 14 日

建立使用者自訂函數。這是一個會傳回值之已儲存的 Transact-SQL 或 Common Language Runtime (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
    這是使用者自訂函數的名稱。函數名稱必須符合識別碼的規則；另外，函數名稱在資料庫內必須是唯一的，且對於它的結構描述也必須是唯一的。

    ms186755.note(zh-tw,SQL.90).gif附註：
    即使沒有指定參數，函數名稱後面仍需要括號。

  • **@**parameter_name
    這是使用者自訂函數中的參數。可以宣告一或多個參數。

    函數最多可以有 1,024 個參數。除非定義了參數的預設值，否則，在執行函數時，使用者必須提供每個已宣告參數的值。

    以 @ 記號 (@) 作為第一個字元，來指定參數名稱。參數名稱必須符合識別碼的規則。對函數而言，參數必須是本機參數；相同的參數名稱可以用在其他函數中。參數只能取代常數，不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。

    ms186755.note(zh-tw,SQL.90).gif附註：
    當在預存程序或使用者自訂函數中傳遞參數時，或在批次陳述式中宣告和設定變數時，會忽略 ANSI_WARNINGS。例如，如果變數定義為 char(3)，然後又設為超過 3 個字元的值，就會將資料截斷成定義的大小，INSERT 或 UPDATE 陳述式會繼續運作。

  • [ type_schema_name**.** ] parameter_data_type
    這是參數資料類型，對於其所屬的結構描述則視情況而定。就 Transact-SQL 函數而言，除了 timestamp 資料類型以外，其他所有資料類型 (包括 CLR 使用者自訂類型) 都是允許的資料類型。就 CLR 函數而言，除了 text、ntext、image 及 timestamp 資料類型以外，其他所有資料類型都是允許的資料類型。非純量類型 cursor 和 table 不能指定為 Transact-SQL 或 CLR 函數中的參數資料類型。

    如果未指定 type_schema_name，SQL Server 2005 Database Engine 會依照下列順序來尋找 scalar_parameter_data_type：

    • 內含 SQL Server 系統資料類型的結構描述。
    • 目前資料庫中之目前使用者的預設結構描述。
    • 目前資料庫的 dbo 結構描述。

  • [ **=**default ]
    這是參數的預設值。如果定義了 default 值，不必指定該參數的值就可以執行函數。

    ms186755.note(zh-tw,SQL.90).gif附註：
    可以針對 CLR 函數指定預設參數值，但 varchar(max) 和 varbinary(max) 資料類型除外。

    如果函數的參數有預設值，則必須在呼叫函數以擷取該預設值時指定關鍵字 DEFAULT。這個行為與使用預存程序中具有預設值的參數不一樣，因為，在預存程序中，省略參數也意味著使用預設值。

  • return_data_type
    這是純量使用者自訂函數的傳回值。就 Transact-SQL 函數而言，除了 timestamp 資料類型以外，其他所有資料類型 (包括 CLR 使用者自訂類型) 都是允許的資料類型。就 CLR 函數而言，除了 text、ntext、image 及 timestamp 資料類型以外，其他所有資料類型都是允許的資料類型。非純量類型 cursor 和 table 不能指定為 Transact-SQL 或 CLR 函數中的傳回資料類型。

  • function_body
    指定 Transact-SQL 陳述式序列 (這些陳述式聯合後不會產生資料表修改之類的副作用) 負責定義函數的值。function_body 只能用在純量函數和多重陳述式資料表值函數中。

    在純量函數中，function_body 是 Transact-SQL 陳述式序列，這些陳述式聯合評估為純量值。

    在多重陳述式資料表值函數中，function_body 是 Transact-SQL 陳述式序列，這些陳述式會擴展 TABLE 傳回變數。

  • scalar_expression
    指定純量函數傳回的純量值。

  • TABLE
    指定資料表值函數的傳回值是資料表。只有常數和 **@**local_variables 可傳遞至資料表值函數。

    在嵌入資料表值函數中，TABLE 傳回值是利用單一 SELECT 陳述式來定義的。內嵌函數沒有相關聯的傳回變數。

    在多重陳述式資料表值函數中，**@**return_variable 是一個 TABLE 變數，可用來儲存及累積應被傳回作為函數值的資料列。只能為 Transact-SQL 函數 (不能為 CLR 函數) 指定 **@**return_variable。

  • 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 識別碼，且必須以指定類別中的靜態方法存在。

    ms186755.note(zh-tw,SQL.90).gif附註：
    依預設，SQL Server 不能執行 CLR 程式碼。您可以建立、修改和卸除參考 Common Language Runtime 模組的資料庫物件；不過，在啟用 clr enabled 選項之前，您無法在 SQL Server 中執行這些參考。若要啟用這個選項，請使用 sp_configure。
  • <table_type_definition>, ( { <column_definition> <column_constraint> ,   | <computed_column_definition> } ,   [ <table_constraint> ] [ ,...n ], ) ,
    定義 Transact-SQL 函數的 table 資料類型。資料表宣告包括資料行定義和資料行或資料表條件約束。資料表一律放在主要檔案群組中。
  • < clr_table_type_definition > , ( { column_namedata_type } [ ,...n ] ),
    定義 CLR 函數的 table 資料類型。資料表宣告只包含資料行名稱和資料類型。資料表一律放在主要檔案群組中。

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

指定函數必須有下列其中一或多個選項。

  • ENCRYPTION
    指出 Database Engine 會將 CREATE FUNCTION 陳述式的原始文字轉換為混亂格式。在 SQL Server 2005 中，無法直接從任何目錄檢視中看見混亂格式的輸出。對系統資料表或資料庫檔案沒有存取權的使用者，無法擷取混亂格式的文字。不過，可以透過 DAC 通訊埠存取系統資料表，或直接存取資料庫檔案的特許使用者，則可使用該文字。另外，可將偵錯工具附加至伺服器處理序的使用者，可以在執行階段從記憶體擷取原始程序。如需有關存取系統中繼資料的詳細資訊，請參閱＜中繼資料可見性組態＞。

    使用此選項可防止在 SQL Server 複寫中發行這個函數。無法為 CLR 函數指定此選項。

  • SCHEMABINDING
    指定函數必須繫結到參考的資料庫物件。如果其他結構描述繫結的物件正在參考函數，這個條件可防止對函數進行變更。

    只有在下發生下列其中一個動作時，才會移除函數與其參考的物件之間的繫結。

    • 已卸除這個函數。
    • 您可以利用未指定 SCHEMABINDING 選項的 ALTER 陳述式來修改函數。

    只有在下列條件為真時，函數才會是結構描述繫結：

    • 函數是一個 Transact-SQL 函數。
    • 函數參考的使用者自訂函數和檢視也是結構描述繫結。
    • 函數參考的物件是利用兩部分名稱來參考的。
    • 函數及其參考的物件屬於相同的資料庫。
    • 執行 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 要利用哪個使用者帳戶來驗證在函數參考的任何資料庫物件上的權限。

    ms186755.note(zh-tw,SQL.90).gif附註：
    無法為內嵌使用者自訂函數指定 EXECUTE AS。

    如需詳細資訊，請參閱＜EXECUTE AS 子句 (Transact-SQL)＞。

< column_definition >::=

定義 table 資料類型。資料表宣告包括資料行定義和條件約束。針對 CLR 函數，只能指定 column_name 和 data_type。

  • column_name
    這是資料表中的資料行名稱。資料行名稱必須符合識別碼規則，且在資料表中必須是唯一的。column_name 可以有 1 到 128 個字元。
  • data_type
    指定資料行資料類型。就 Transact-SQL 函數而言，除了 timestamp 以外，其他所有資料類型 (包括 CLR 使用者自訂類型) 都是允許的資料類型。就 CLR 函數而言，除了 text、ntext、image、char、varchar、varchar(max) 及 timestamp 以外，其他所有資料類型都是允許的資料類型。無法指定非純量類型 cursor 作為 Transact-SQL 或 CLR 函數中的資料行資料類型。
  • DEFAULT constant_expression
    指定在插入期間未明確提供值時，提供給資料行的值。constant_expression 是常數、NULL 或系統函數值。除了含有 IDENTITY 屬性的資料行之外，任何資料行都可以套用 DEFAULT 定義。無法為 CLR 資料表值函數指定 DEFAULT。

  • COLLATE collation_name
    指定資料行的定序。若未指定，就會將資料庫的預設定序指派給資料行。定序名稱可以是 Windows 定序名稱或 SQL 定序名稱。如需定序清單以及有關定義的詳細資訊，請參閱＜Windows 定序名稱 (Transact-SQL)＞和＜SQL 定序名稱 (Transact-SQL)＞。

    COLLATE 子句只可用來變更 char、varchar、nchar 及 nvarchar 資料類型之資料行的定序。

    無法為 CLR 資料表值函數指定 COLLATE。

  • ROWGUIDCOL
    指出新資料行是一個資料列全域唯一識別碼資料行。每份資料表都只能有一個 uniqueidentifier 資料行指定為 ROWGUIDCOL 資料行。ROWGUIDCOL 屬性只能指派給 uniqueidentifier 資料行。

    ROWGUIDCOL 屬性不會強制執行資料行所儲存之值的唯一性。它也不自動為插入資料表中的新資料列產生值。若要為每個資料行產生唯一值，請在 INSERT 陳述式上使用 NEWID 函數。可以指定預設值；不過，NEWID 不能指定為預設值。

  • IDENTITY
    指出新資料行是識別資料行。當新資料列加入資料表時，SQL Server 會提供資料行的唯一累加值。識別資料行通常用來搭配 PRIMARY KEY 條件約束一起使用，作為資料表的唯一資料列識別碼。IDENTITY 屬性可以指派給 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 等資料行。每份資料表都只能建立一個識別資料行。繫結的預設值和 DEFAULT 條件約束，無法搭配識別資料行來使用。您必須同時指定 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
    這是一項條件約束，它利用唯一索引強制執行指定資料行的實體完整性。在資料表值使用者自訂函數中，PRIMARY KEY 條件約束只能建立在每份資料表的一個資料行上。無法為 CLR 資料表值函數指定 PRIMARY KEY。
  • UNIQUE
    這是一項條件約束，它利用唯一索引為指定資料行提供實體完整性。一份資料表可以有多項 UNIQUE 條件約束。無法為 CLR 資料表值函數指定 UNIQUE。

  • CLUSTERED | NONCLUSTERED
    指出針對 PRIMARY KEY 或 UNIQUE 條件約束建立叢集或非叢集索引。PRIMARY KEY 條件約束使用 CLUSTERED，UNIQUE 條件約束則使用 NONCLUSTERED。

    只能為一個條件約束指定 CLUSTERED。如果針對 UNIQUE 條件約束指定 CLUSTERED，且指定了 PRIMARY KEY 條件約束，則 PRIMARY KEY 會使用 NONCLUSTERED。

    無法為 CLR 資料表值函數指定 CLUSTERED 和 NONCLUSTERED。

  • CHECK
    這是一個條件約束，藉由限制可能輸入一或多個資料行的值，強制執行範圍完整性。無法為 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
    指定百分比來指出在建立或變更索引期間，Database Engine 應該使各索引頁面之分葉層級填滿的程度。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 陳述式。
  • 如需詳細資訊，請參閱＜建立使用者自訂函數 (Database Engine)＞。

建立使用者自訂函數的巢狀結構

使用者自訂函數可以具有巢狀結構；也就是，使用者自訂函數可以呼叫另一個使用者自訂函數。被呼叫的函數開始執行時，巢狀層級會遞增；被呼叫的函數完成執行時，巢狀層級會遞減。使用者自訂函數所建立的巢狀結構最多可以有 32 個層級。超過巢狀層級上限會導致整個呼叫函數鏈結失敗。

ms186755.note(zh-tw,SQL.90).gif附註：
依照 32 個層級巢狀限制，Transact-SQL 使用者自訂函數之 Managed 程式碼的任何參考都算是一個層級。從 Managed 程式碼內叫用的方法，不列入這項限制。

函數屬性

在舊版的 SQL Server 中，函數只有具決定性或不具決定性兩種分類。在 SQL Server 2005 中，函數有下列屬性。這些屬性的值會決定是否可以在可保存或索引的計算資料行中使用函數。

屬性 描述 附註

IsDeterministic

函數可分為具決定性或不具決定性。

具決定性函數中允許本機資料存取。例如，每當利用一組特定輸入值來呼叫函數時都一律傳回相同結果且含有相同資料庫狀態的函數，就會被標示為具決定性。

IsPrecise

函數可分為精確或不精確。

不精確函數內含浮點作業之類的作業。

IsSystemVerified

SQL Server 可以驗證函數的有效位數和決定性屬性。

 

SystemDataAccess

函數存取 SQL Server 之本機執行個體中的系統資料 (系統目錄或虛擬系統資料表)。

 

UserDataAccess

函數存取 SQL Server 之本機執行個體中的使用者資料。

併入使用者自訂資料表和暫存資料表，但不併入資料表變數。

SQL Server 會自動判斷 Transact-SQL 函數的有效位數和決定性屬性。如需詳細資訊，請參閱＜使用者自訂函數設計指導方針＞。使用者可以指定 CLR 函數的資料存取和決定性屬性。如需詳細資訊，請參閱＜Overview of CLR Integration Custom Attributes＞。

若要顯示這些屬性目前的值，請使用 OBJECTPROPERTYEX。

索引叫用使用者自訂函數的計算資料行

當使用者自訂函數有下列屬性值時，可以在索引中使用叫用使用者自訂函數的計算資料行。

  • IsDeterministic = true
  • IsSystemVerified = true (除非計算資料行是保存的)
  • UserDataAccess = false
  • SystemDataAccess = false

如需詳細資訊，請參閱＜在計算資料行上建立索引＞。

從函數呼叫擴充預存程序

從函數內呼叫擴充預存程序時，擴充預存程序無法將結果集傳回用戶端。任何將結果集傳回用戶端的 ODS API 都會傳回 FAIL。擴充預存程序可能會往回連接到 SQL Server 的執行個體；不過，它不應該嘗試將相同的交易聯結為叫用擴充預存程序的函數。

與從批次或預存程序進行的引動過程相似，擴充預存程序會在執行 SQL Server 的 Windows 安全性帳戶時所在的內容中執行。當預存程序的擁有者將在預存程序上的 EXECUTE 權限提供給使用者時，該擁有者應考量前述的情形。

函數引動過程

純量值函數可在使用純量運算式的情況下被叫用。這包括計算資料行和 CHECK 條件約束定義。您也可以利用 EXECUTE 陳述式來執行純量值函數。叫用純量值函數必須至少使用函數的兩部分名稱。如需有關多部分名稱的詳細資訊，請參閱＜Transact-SQL 語法慣例 (Transact-SQL)＞。在 SELECT、INSERT、UPDATE 或 DELETE 陳述式的 FROM 子句中允許資料表運算式的情況下，就可以叫用資料表值函數。如需詳細資訊，請參閱＜執行使用者自訂函數 (Database Engine)＞。

使用 CLR 函數中的參數和傳回值

如果在 CLR 函數中指定參數，這些參數應該是 SQL Server 類型，如先前針對 scalar_parameter_data_type 所下的定義所示。如需有關比較 SQL Server 系統資料類型和 CLR 整合資料類型，或 .NET Framework Common Language Runtime 資料類型的資訊，請參閱＜SQL Server Data Types and Their .NET Framework Equivalents＞。

當正確的方法在類別中多載時，若要讓 SQL Server 參考正確的方法，<method_specifier> 中所指出的方法必須具有下列性質：

  • 接收 [ ,...n ] 中所指定相同數目的參數。
  • 依值 (而不是依參考) 接收所有參數。
  • 使用與 SQL Server 函數中指定之類型相容的參數類型。

如果 CLR 函數的傳回資料類型有指定資料表類型 (RETURNS TABLE)，<method_specifier> 中之方法的傳回資料類型應該屬於 IEnumerator 或 IEnumerable 類型，且假設介面是由函數建立者所實作的。與 Transact-SQL 函數不同，CLR 函數不能將 PRIMARY KEY、UNIQUE 或 CHECK 條件約束併入 <table_type_definition> 中。<table_type_definition> 中指定的資料行資料類型必須符合相對應的資料行類型；該資料行是 <method_specifier> 中的方法在執行階段傳回之結果集資料行。這項類型檢查作業不是在建立函數時執行的。

如需有關程式設計 CLR 函數的詳細資訊，請參閱＜CLR User-Defined Functions＞。

不允許的 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
ms186755.note(zh-tw,SQL.90).gif附註：
利用 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. 建立嵌入資料表值函數

下列範例傳回嵌入資料表值函數。它傳回三個資料行：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)。當提供有效的員工識別碼時，函數會傳回對應於所有員工的資料表，該資料表會直接或間接報告至員工。函數利用遞迴一般資料表運算式 (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 Database Engine 範例 安裝在本機電腦的預設位置中，且已編譯 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 Table-Valued Functions＞。

請參閱

參考

ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)

其他資源

使用者自訂函數 (Database Engine)
CLR User-Defined Functions

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2006 年 4 月 14 日
新增內容：
  • 在「備註」一節的「函數引動過程」下，新增叫用純量值函數必須至少使用函數的兩部分名稱。

2005 年 12 月 5 日
變更的內容：
  • 釐清 ENCRYPTION 選項的定義。