共用方式為


CREATE PROCEDURE (Transact-SQL)

建立預存程序。預存程序是已儲存的 Transact-SQL 陳述式集合,或可取用及傳回使用者提供之參數的 Microsoft .NET Framework Common Language Runtime (CLR) 方法的參考。程序建立後可作為永久使用或在工作階段內作為暫時使用 (本機暫存程序),或者,可在所有工作階段內作為暫時使用 (全域暫存程序)。

預存程序建立後也可在 SQL Server 的執行個體啟動時自動執行。

主題連結圖示Transact-SQL 語法慣例

語法

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

引數

  • schema_name
    這是程序所屬的結構描述名稱。

  • procedure_name
    這是新預存程序的名稱。程序名稱必須符合識別碼的規則,且在結構描述內必須是唯一的。

    強烈建議您不要在程序名稱中使用前置詞 sp_。這個前置詞是供 SQL Server 指定系統預存程序時使用。如需詳細資訊,請參閱<建立預存程序 (Database Engine)>。

    針對本機暫存程序,可以在 procedure_name 前面利用一個數字符號 (#) 來建立本機暫存程序 (#procedure_name);針對全域暫存程序,可以在前面加上兩個數字符號來建立全域暫存程序 (##procedure_name)。無法為 CLR 預存程序指定暫存名稱。

    預存程序或全域暫存預存程序的完整名稱 (包括 ##) 不能超過 128 個字元。本機暫存預存程序的完整名稱 (包括 #) 不能超過 116 個字元。

  • **;**number
    這是用來將同名程序分組的選擇性整數。您可以利用一個 DROP PROCEDURE 陳述式一併卸除這些分組的程序。例如,一個稱為 orders 的應用程式可能使用名為 orderproc;1orderproc;2 等等的程序。DROP PROCEDURE orderproc 陳述式可以卸除整個群組。如果名稱包含分隔識別碼,則不應該併入號碼做為識別碼的一部分;請只在 procedure_name 前後使用適當的分隔符號。

    編號預存程序有下列限制:

    • 不能使用 xml 或 CLR 使用者定義型別做為資料類型。

    • 不能在編號的預存程序上建立計畫指南。

    [!附註]

    未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。

  • **@**parameter
    這是程序中的參數。您可以在 CREATE PROCEDURE 陳述式中宣告一個或多個參數。除非定義了參數的預設值或將值設為等於其他參數,否則,在呼叫程序時,使用者必須提供每個已宣告參數的值。預存程序最多可以有 2,100 個參數。如果某個程序包含資料表值參數,而且此參數在呼叫中遺漏,就會傳入空白資料表的預設值。

    使用 @ 記號 (@) 當做第一個字元來指定參數名稱。參數名稱必須符合識別碼的規則。對程序而言,參數必須是本機參數;相同的參數名稱可以用在其他程序中。依預設,參數只能取代常數運算式,不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。如需詳細資訊,請參閱<EXECUTE (Transact-SQL)>。

    如果指定了 FOR REPLICATION,就不能宣告參數。

  • [ type_schema_name**.** ] data_type
    這是參數資料類型及其所屬的結構描述。所有資料類型都可以當做 Transact-SQL 預存程序的參數。您可以使用使用者定義資料表類型,將資料表值參數宣告為 Transact-SQL 預存程序的參數。資料表值參數只能指定為輸入參數,而且必須與 READONLY 關鍵字一起使用。cursor 資料類型只能用在 OUTPUT 參數。當您指定 cursor 資料類型時,您也必須指定 VARYING 和 OUTPUT 關鍵字。您可以搭配 cursor 資料類型,指定多個輸出參數。

    如果是 CLR 預存程序,不能指定 charvarchartextntextimagecursor、使用者定義資料表類型和 table 做為參數。如需有關 CLR 類型與 SQL Server 系統資料類型之間對應的詳細資訊,請參閱<對應 CLR 參數資料>。如需有關 SQL Server 系統資料類型及其語法的詳細資訊,請參閱<資料類型 (Transact-SQL)>。

    如果參數的資料類型是 CLR 使用者定義型別,您必須具有在該類型上的 EXECUTE 權限。

    如果未指定 type_schema_name,SQL Server Database Engine 會依照下列順序來參考 type_name:

    • SQL Server 系統資料類型。

    • 目前資料庫中之目前使用者的預設結構描述。

    • 目前資料庫中的 dbo 結構描述。

    如果是編號的預存程序,資料類型不能是 xml 或 CLR 使用者定義型別。

  • VARYING
    指定當做輸出參數支援的結果集。這個參數由預存程序動態建構,可能會有不同的內容。只適用於 cursor 參數。

  • default
    這是參數的預設值。如果定義了 default 值,不必指定該參數的值就可以執行程序。預設值必須是常數,或者,可以是 NULL。如果程序搭配 LIKE 關鍵字使用參數,則可包含下列萬用字元:% _ [] 及 [^]。

    [!附註]

    sys.parameters.default 資料行中只會記錄 CLR 程序的預設值。如果是 Transact-SQL 程序參數,該資料行則是 NULL。

  • OUTPUT
    指出這個參數是輸出參數。可以將這個選項的值傳回呼叫的 EXECUTE 陳述式。您可以利用 OUTPUT 參數將值傳回程序的呼叫者。除非程序是 CLR 程序,否則 text、ntext 及 image 參數不能當做 OUTPUT 參數。除非程序是一個 CLR 程序,否則,使用 OUTPUT 關鍵字的輸出參數可以是資料指標預留位置。使用者定義資料表類型無法指定為預存程序的 OUTPUT 參數。

  • READONLY
    表示無法在程序的主體內更新或修改參數。如果參數類型是使用者定義資料表類型,就必須指定 READONLY。

  • RECOMPILE
    表示 Database Engine 不快取這個程序的計畫,且程序是在執行階段編譯的。當指定 FOR REPLICATION 時,不能使用這個選項。無法為 CLR 預存程序指定 RECOMPILE。

    若要指示 Database Engine 捨棄預存程序內個別查詢的計畫,請使用 RECOMPILE 查詢提示。如需詳細資訊,請參閱<查詢提示 (Transact-SQL)>。如果只在屬於預存程序的查詢子集中使用非典型值或暫存值,請使用 RECOMPILE 查詢提示。

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

    這個選項不適用於 CLR 預存程序。

    利用這個選項建立的程序,不能發行為 SQL Server 複寫的一部分。

  • EXECUTE AS
    指定執行預存程序時所在的安全性內容。

    如需詳細資訊,請參閱<EXECUTE AS 子句 (Transact-SQL)>。

  • FOR REPLICATION
    指定無法針對訂閱者執行為複寫而建立的預存程序。利用 FOR REPLICATION 選項建立的預存程序,用來做為預存程序篩選,而且只有在複寫期間才會執行它。如果指定了 FOR REPLICATION,就不能宣告參數。無法為 CLR 預存程序指定 FOR REPLICATION。利用 FOR REPLICATION 建立的程序會忽略 RECOMPILE 選項。

    FOR REPLICATION 程序在 sys.objectssys.procedures 中會有物件類型 RF

  • <sql_statement>
    這是包含在程序中的一個或多個 Transact-SQL 陳述式。如需有關某些適用限制的資訊,請參閱<備註>一節。

  • EXTERNAL NAME assembly_name**.class_name.method_name
    指定 CLR 預存程序所參考之 .NET Framework 組件的方法。class_name 必須是有效的 SQL Server 識別碼,且必須當做類別存在於組件中。如果類別包含符合命名空間的名稱 (使用句號 (
    .) 來分隔命名空間的各個部分),您就必須使用方括號 ([]) 或引號 (""**) 來分隔類別名稱。指定的方法必須是類別的靜態方法。

    [!附註]

    依預設,SQL Server 不能執行 CLR 程式碼。您可以建立、修改和卸除參考 Common Language Runtime 模組的資料庫物件;不過,在啟用 clr enabled 選項之前,您無法在 SQL Server 中執行這些參考。若要啟用這個選項,請使用 sp_configure

備註

預存程序沒有預先定義的大小上限。

使用者定義預存程序只能在目前的資料庫中建立。暫存程序是這項規定的例外狀況,因為暫存程序一律在 tempdb 中建立。如果未指定結構描述名稱,就會使用要建立程序之使用者的預設結構描述。如需有關結構描述的詳細資訊,請參閱<使用者結構描述分隔>。

CREATE PROCEDURE 陳述式無法在單一批次中,與其他 Transact-SQL 陳述式結合。

依預設,參數可為 Null。如果傳遞了 NULL 參數值,且在參考的資料行不允許 NULL 值的 CREATE TABLE 或 ALTER TABLE 陳述式中使用該參數,則 Database Engine 會產生錯誤。若要防止將 NULL 傳遞至不允許 NULL 值的資料行,請將程式設計邏輯加入至程序中,或藉由 CREATE TABLE 或 ALTER TABLE 的關鍵字 DEFAULT 來使用資料行的預設值。

建議您為暫存資料表中的每個資料行明確指定 NULL 或 NOT NULL。當 CREATE TABLE 或 ALTER TABLE 陳述式中沒有指定 NULL 或 NOT NULL 屬性時,ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項可控制 Database Engine 將這些屬性指派給資料行的方式。如果某連接執行預存程序時所用的選項設定,不同於建立程序的連接所用設定,針對第二個連接建立的資料表資料行,可以有不同的 Null 屬性,且可以表現不同的行為。如果針對每個資料行明確陳述 NULL 或 NOT NULL,就會利用執行預存程序之所有連接的相同 Null 屬性來建立暫存資料表。

使用 SET 選項

當建立或修改 Transact-SQL 預存程序時,Database Engine 會將 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設定一併儲存。這些原始設定是在執行預存程序時使用的。因此,當預存程序正在執行時,SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的任何用戶端工作階段設定都會遭到忽略。當建立或修改預存程序時,不會儲存其他 SET 選項,例如:SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS。如果預存程序的邏輯取決於特定設定,請在程序的開頭加入 SET 陳述式,以確保可以有適當的設定。如果從預存程序中執行 SET 陳述式,設定的有效性只能維持到預存程序完成執行。之後,該設定就會還原為預存程序被呼叫時所具有的值。這可讓個別用戶端設定本身想要的選項,而不影響預存程序的邏輯。

[!附註]

在預存程序或使用者定義函數中傳遞參數,或在批次陳述式中宣告和設定變數時,不接受 ANSI_WARNINGS。例如,如果變數定義為 char(3),然後又設為超過 3 個字元的值,就會將資料截斷成定義的大小,而 INSERT 或 UPDATE 陳述式會繼續運作。

搭配 CLR 預存程序使用參數

CLR 預存程序的參數可以是任何一種純量 SQL Server 系統資料類型。

在 .NET Framework 中多載正確的方法時,若要讓 Database Engine 參考正確的方法,<method_specifier> 中所指出的方法必須具有下列特性:

  • 宣告為靜態方法。

  • 接收與程序的參數數目相同的參數數目。

  • 不是其類別的建構函式或解構函式。

  • 使用與 SQL Server 程序之相對應參數的資料類型相容的參數類型。如需有關使 SQL Server 資料類型符合 .NET Framework 資料類型的詳細資訊,請參閱<對應 CLR 參數資料>。

  • 傳回空值,或傳回 SQLInt32、SQLInt16、System.Int32 或 System.Int16 類型的值。

  • 如果針對任何特定參數宣告指定 OUTPUT,就會依參考 (而不是依值) 傳回它的參數。

取得預存程序的相關資訊

若要顯示 Transact-SQL 預存程序的定義,請使用程序所在資料庫中的 sys.sql_modules 目錄檢視。

例如:

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

[!附註]

利用 ENCRYPTION 選項建立的預存程序文字無法利用 sys.sql_modules 目錄檢視來檢視。

如需程序參考之物件的相關報告,請查詢 sys.sql_expression_dependencies 目錄檢視,或使用 sys.dm_sql_referenced_entitiessys.dm_sql_referencing_entities

若要顯示 CLR 預存程序的相關資訊,請使用程序所在資料庫中的 sys.assembly_modules 目錄檢視。

若要顯示預存程序中定義之參數的相關資訊,請使用程序所在資料庫中的 sys.parameters 目錄檢視。

延遲名稱解析

您可以建立參考尚不存在之資料表的預存程序。在建立時,只會執行語法檢查。直到第一次執行預存程序時,才會編譯預存程序。只有在編譯期間才會解析預存程序中參考的所有物件。因此,仍可順利建立參考不存在之資料表中語意正確的預存程序;不過,如果參考的資料表不存在,預存程序在執行階段將會失敗。如需詳細資訊,請參閱<延遲名稱解析及編譯>。

執行預存程序

當您執行使用者定義預存程序時,在批次中或在使用者定義預存程序或函數之類的模組內,強烈建議您利用結構描述名稱來限定預存程序名稱。

若要寫入預存程序來接受參數值,則可以提供參數值。所提供的值必須是常數或變數。您不能指定函數名稱做為參數值。變數可以是使用者自訂變數或系統變數,如 @@SPID。

如需詳細資訊,請參閱<執行預存程序 (Database Engine)>。

第一次執行程序時,會編譯它來決定擷取資料的最佳存取計畫。如果已經產生的計畫仍保留在 Database Engine 的計畫快取中,則預存程序的後續執行作業可以重複使用該計畫。如需詳細資訊,請參閱<執行計畫快取與重複使用>。

使用 cursor 資料類型的參數

Transact-SQL 預存程序只能針對 OUTPUT 參數使用 cursor 資料類型。如果為參數指定 cursor 資料類型,則同時需要 VARYING 和 OUTPUT 參數。如果為參數指定 VARYING 關鍵字,資料類型必須是 cursor,且必須指定 OUTPUT 關鍵字。如需詳細資訊,請參閱<在 OUTPUT 參數中使用 cursor 資料類型>。

暫存預存程序

Database Engine 支援兩種暫存程序類型:本機和全域。只有建立本機暫存程序的連接可以看到該程序。全域暫存程序可供所有連接使用。當目前的工作階段結束時,會自動卸除本機暫存程序。當最後一個使用全域暫存程序的工作階段結束時,會卸除該程序。如需詳細資訊,請參閱<建立預存程序 (Database Engine)>。

自動執行預存程序

當 SQL Server 啟動時,會自動執行一個或多個預存程序。預存程序必須由 master 資料庫中的系統管理員建立,且必須在系統管理員 (sysadmin) 固定伺服器角色下執行為背景處理序。這些程序不得有任何輸入或輸出參數。如需詳細資訊,請參閱<自動執行預存程序>。

建立預存程序的巢狀結構

預存程序可以有巢狀結構。這表示預存程序可以呼叫另一個預存程序。被呼叫的程序開始執行時,巢狀層級會遞增;被呼叫的程序完成執行時,巢狀層級會遞減。預存程序所建立的巢狀結構最多可以有 32 個層級。如需詳細資訊,請參閱<巢狀預存程序>。

若要預估已編譯的預存程序大小,請使用下列效能監視器計數器。

效能監視器物件名稱

效能監視器計數器名稱

SQLServer:計畫快取物件

快取命中率

 

快取頁面

 

快取物件計數*

* 這些計數器可供各種類別目錄的快取物件使用,包括特定 sql、已備妥的 sql、程序、觸發程序等等。

如需詳細資訊,請參閱<SQL Server 的 Plan Cache 物件>。

<sql_statement> 限制

除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外,任何 SET 陳述式都可以在預存程序中指定。這些陳述式必須是批次中唯一的陳述式。所選 SET 選項在預存程序執行期間仍然有效,然後會還原為先前的設定。

如果是預存程序擁有者以外的使用者要使用預存程序,則必須在預存程序內,利用物件結構描述的名稱來指定所有資料定義語言 (DDL) 陳述式 (如 CREATE、ALTER 或 DROP 陳述式、DBCC 陳述式、EXECUTE 和動態 SQL 陳述式) 中使用的物件名稱。如需詳細資訊,請參閱<設計預存程序 (Database Engine)>。

權限

需要資料庫中的 CREATE PROCEDURE 權限,以及建立程序時所在結構描述上的 ALTER 權限。

如果是 CLR 預存程序,需要 <method_specifier> 中參考之組件的擁有權,或在該組件上的 REFERENCES 權限。

範例

A. 使用簡單的程序

下列預存程序從檢視表傳回所有員工 (所提供的姓氏和名字)、工作職稱及部門名稱。這個預存程序沒有使用任何參數。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

您可以利用下列方式執行 uspGetEmployees 預存程序:

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. 搭配參數使用簡單的程序

下列預存程序只從檢視表傳回指定的員工 (所提供的姓氏和名字)、職稱及部門名稱。這個預存程序接受與傳遞的參數完全相符的項目。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

您可以利用下列方式執行 uspGetEmployees 預存程序:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C. 搭配萬用字元參數使用簡單的程序

下列預存程序只從檢視表傳回指定的員工 (所提供的姓氏和名字)、職稱及部門。這個預存程序模式符合傳遞的參數,或者,如果沒有提供,該模式會使用預設值 (以字母 D 為開頭的姓氏)。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

您可以利用多種組合執行 uspGetEmployees2 預存程序。以下只顯示數種組合:

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D. 傳回一個以上的結果集。

下列預存程序會傳回兩個結果集。

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

E. 使用 OUTPUT 參數

下列範例會建立 uspGetList 預存程序。這個程序會傳回一份產品清單,其中產品的價格都沒有超過指定的金額。這個範例顯示多重 SELECT 陳述式和多重 OUTPUT 參數的用法。OUTPUT 參數可讓外部程序、批次,或多個 Transact-SQL 陳述式在程序執行期間存取值集。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

執行 uspGetList 以傳回成本低於 $700 的 Adventure Works 產品 (自行車) 清單。搭配流程控制語言使用 OUTPUT 參數 @Cost@ComparePrices,即可在 [訊息] 視窗中傳回訊息。

[!附註]

建立程序以及使用變數時,都必須定義 OUTPUT 變數。參數名稱與變數名稱不一定要相符;不過,除非使用 @ListPrice= variable,否則,資料類型與參數定位必須相符。

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

部分結果集如下:

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

F. 使用 WITH RECOMPILE 選項

如果提供給程序的參數不是典型的參數,且不應該在記憶體中快取或儲存新的執行計畫,WITH RECOMPILE 子句十分好用。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

G. 使用 WITH ENCRYPTION 選項

下列範例會建立 HumanResources.uspEncryptThis 預存程序。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

WITH ENCRYPTION 選項會防止傳回預存程序的定義,如下列範例所示。

執行 sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

以下為結果集:

The text for object 'HumanResources.uspEncryptThis' is encrypted.

直接查詢 sys.sql_modules 目錄檢視:

USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

以下為結果集:

definition
----------------------
NULL

(1 row(s) affected)

H. 使用延遲名稱解析

下列範例會建立 uspProc1 程序。它會使用延遲名稱解析。雖然在編譯時參考的資料表不存在,仍會建立預存程序。不過,在執行程序時,資料表必須存在。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

若要確認已建立預存程序,請執行下列查詢:

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

以下為結果集:

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

I. 使用 EXECUTE AS 子句

下列範例顯示如何利用 EXECUTE AS 子句來指定可執行預存程序的安全性內容。在這個範例中,選項 CALLER 指定可以在呼叫程序的使用者之內容中執行程序。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

J. 建立 CLR 預存程序

下列範例會建立 GetPhotoFromDB 預存程序,這個預存程序參考 HandlingLOBUsingCLR 組件之 LargeObjectBinary 類別的 GetPhotoFromDB 方法。建立預存程序之前,會先在本機資料庫中註冊 HandlingLOBUsingCLR 組件。

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

K. 使用 OUTPUT 資料指標參數

OUTPUT 資料指標參數用來將一個特定資料指標 (對預存程序而言,它是本機資料指標) 往回傳遞至呼叫批次、預存程序或觸發程序。

首先,建立宣告的程序,然後在 Currency 資料表上開啟資料指標:

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

接著,執行宣告本機資料指標變數的批次、執行程序將資料指標指派給本機變數,然後從資料指標提取資料列。

USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

請參閱

工作

參考

概念

其他資源