CREATE PROCEDURE (Transact-SQL)

語法

--Transact-SQL Stored Procedure Syntax
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 { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

--CLR Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { 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 陳述式一併卸除這些分組的程序。

  [!附註]

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

  編號程序無法使用 xml 或 CLR 使用者定義型別，而且無法在計畫指南中使用。

• @parameter
  在程序中宣告的參數。使用 at 記號 (@) 當做第一個字元來指定參數名稱。參數名稱必須符合識別碼的規則。對程序而言，參數必須是本機參數；相同的參數名稱可以用在其他程序中。

  您可以宣告一個或多個參數，最大值為 2,100。除非定義了參數的預設值或將值設為等於其他參數，否則，在呼叫程序時，使用者必須提供每個已宣告參數的值。如果程序包含 資料表值參數，而且此參數在呼叫中遺漏，就會傳入空白資料表。參數只能取代常數運算式，不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。如需詳細資訊，請參閱＜EXECUTE (Transact-SQL)＞。

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

• [ type_schema_name**.**] data_type
  參數資料類型及該資料類型所屬的結構描述。

  Transact-SQL 程序的資料類型指導方針：

  • 所有 Transact-SQL 資料類型都可以當做參數使用。

  • 您可以使用使用者定義資料表類型建立資料表值參數。資料表值參數只能是 INPUT 參數，而且必須與 READONLY 關鍵字一起使用。如需詳細資訊，請參閱＜資料表值參數 (Database Engine)＞。

  • cursor 資料類型只能是 OUTPUT 參數，而且必須與 VARYING 關鍵字一起使用。

  CLR 程序的資料類型指導方針：

  • 在 Managed 程式碼中具有對等類型的所有原生 SQL Server 資料類型都可以當做參數使用。如需有關 CLR 類型與 SQL Server 系統資料類型之間對應的詳細資訊，請參閱＜對應 CLR 參數資料＞。如需有關 SQL Server 系統資料類型及其語法的詳細資訊，請參閱＜資料類型 (Transact-SQL)＞。

  • 資料表值或 cursor 資料類型都無法當做參數使用。

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

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

• default
  參數的預設值。如果定義了參數的預設值，不必指定該參數的值就可以執行程序。預設值必須是常數，或者，可以是 NULL。常數值可以採用萬用字元格式，讓您可以在將參數傳入程序時使用 LIKE 關鍵字。請參閱下面的範例 C。

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

• OUT | OUTPUT
  指出這個參數是輸出參數。您可以利用 OUTPUT 參數，將值傳回程序的呼叫端。除非程序是 CLR 程序，否則 text、ntext 及 image 參數不能當做 OUTPUT 參數使用。除非此程序是一個 CLR 程序，否則輸出參數可以當做資料指標預留位置使用。資料表值資料類型無法指定為程序的 OUTPUT 參數。

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

• RECOMPILE
  指出 Database Engine 不快取這個程序的查詢計畫，以強制在每次執行該程序時進行編譯。如需有關強制重新編譯之原因的詳細資訊，請參閱＜重新編譯預存程序＞。指定 FOR REPLICATION 時，或是 CLR 程序時，無法使用此選項。

  若要指示 Database Engine 捨棄程序內之個別查詢的查詢計畫，請使用查詢定義中的 RECOMPILE 查詢提示。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

• 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.objects 和 sys.procedures 中會有物件類型 RF。

• { [ BEGIN ] sql_statement [;] [ ...n ][ END ] }
  包含程序主體的一個或多個 Transact-SQL 陳述式。您可以使用選用的 BEGIN 和 END 關鍵字來括住陳述式。如需詳細資訊，請參閱以下的＜最佳作法＞、＜一般備註＞以及＜限制事項＞這幾節。

• 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。

最佳作法

雖然這不是最詳盡的最佳作法清單，但這些建議可能會改善程序效能。

• 使用 SET NOCOUNT ON 陳述式做為程序主體中的第一個陳述式。亦即，將該陳述式放在 AS 關鍵字正後方。這樣會關閉 SQL Server 在執行任何 SELECT、INSERT、UPDATE、MERGE 和 DELETE 陳述式之後，傳回用戶端的訊息。排除這不必要的網路負擔會改善資料庫和應用程式的整體效能。如需詳細資訊，請參閱＜SET NOCOUNT (Transact-SQL)＞。

• 建立或參考程序中的資料庫物件時，請使用結構描述名稱。如果 Database Engine 不必搜尋多個結構描述，解析物件名稱所需的處理時間會較少。在不指定結構描述的情況下建立物件時，它也會防止使用者預設結構描述所引起的權限和存取問題遭到指派。如需詳細資訊，請參閱＜使用者結構描述分隔＞。

• 避免在 WHERE 和 JOIN 子句中指定的資料行周圍使用包裝函式。這麼做會使資料行變成非決定性，而且會使查詢處理器無法使用索引。

• 避免在 SELECT 陳述式中使用傳回許多資料列的純量函數。純量函數必須套用到每個資料列，因此，所產生的行為類似以資料列為主的處理，而且會降低效能。

• 請避免使用 SELECT *。請改為指定所需的資料行名稱。這樣可以防止停止程序執行的部分 Database Engine 錯誤。例如，從 12 資料行資料表傳回資料並將該資料插入 12 資料行暫存資料表的 SELECT * 陳述式，在變更資料表中資料行的數目或順序之前將會成功。

• 請避免處理或傳回太多資料。盡早將程序程式碼中的結果範圍縮小，讓該程序所執行的所有後續作業都可以使用最小的資料集完成。僅將基本資料傳送到用戶端應用程式。這種作法比透過網路傳送額外資料並強制用戶端應用程式處理過大的結果集更有效率。

• 透過 BEGIN/END TRANSACTION 使用明確的交易，並使交易維持越短越好。交易越久表示記錄鎖定越久，而且發生死結的可能性也就越大。如需詳細資訊，請參閱＜鎖定與資料列版本控制＞、＜鎖定相容性 (Database Engine)＞或＜Database Engine 中的隔離等級＞。

• 請針對程序內部的錯誤處理使用 Transact-SQL TRY…CATCH 功能。TRY…CATCH 可以封裝 Transact-SQL 陳述式的整個區塊。這樣不但會使效能負擔較小，而且還會使用更少的程式讓錯誤報告更精確。如需詳細資訊，請參閱＜在 Transact-SQL 中使用 TRY...CATCH＞。

• 在程序主體中 CREATE TABLE 或 ALTER TABLE Transact-SQL 陳述式所參考的所有資料表資料行上使用 DEFAULT 關鍵字。這會防止將 NULL 傳遞到不允許 null 值的資料行。

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

• 使用轉換 null 的修改陳述式，然後使用查詢中的 null 值加入排除資料列的邏輯。請注意，在 Transact-SQL 中，NULL 不是空的或 “nothing” 值。它是未知值的預留位置，而且可能造成非預期的行為，特別是在查詢結果集或使用 AGGREGATE 函數時。如需詳細資訊，請參閱＜NULL 比較搜尋條件＞和＜Null 值＞。

• 除非不同的值有特定需要，否則請使用 UNION ALL 運算子代替 UNION 或 OR 運算子。UNION ALL 運算子需要的處理負擔較少，因為不會從結果集中篩選出重複項目。

一般備註

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

程序內部的變數可以是使用者自訂變數或系統變數，如 @@SPID。

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

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

當某個程序參考 CLR 常式、類型或彙總，藉以呼叫另一個程序或執行 Managed 程式碼時，這些程序就是巢狀程序。程序和 Managed 程式碼參考的巢狀結構最多可以有 32 個層級。被呼叫的程序或 Managed 程式碼參考開始執行時，巢狀層級加一，而被呼叫的程序或 Managed 程式碼參考執行完畢時，巢狀層級就減一。從 Managed 程式碼內部叫用的方法不列入巢狀層級限制。不過，當 CLR 預存程序透過 SQL Server Managed 提供者執行資料存取作業時，就會在 Managed 程式碼轉換成 SQL 的過程中加入一個額外的巢狀層級。

嘗試超過最大巢狀層級會導致整個呼叫鏈結失敗。您可以使用 @@NESTLEVEL 函數來傳回目前預存程序執行的巢狀層級。

互通性

當建立或修改 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 陳述式，設定的有效性只能維持到程序完成執行。之後，該設定就會還原為程序被呼叫時所具有的值。這可讓個別用戶端設定本身想要的選項，而不影響程序的邏輯。

除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外，其他所有 SET 陳述式都可以在程序中指定。這些陳述式必須是批次中唯一的陳述式。所選 SET 選項在程序執行期間仍然有效，然後會還原為先前的設定。如需有關 SET 選項的詳細資訊，請參閱＜SET 選項＞。

限制事項

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

下列陳述式無法在預存程序主體中的任何位置使用。

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

執行程序時，您無法將函數名稱指定為參數預設值或傳遞至參數的值。不過，您可以傳遞函數成為變數，如下列範例所示。

-- Passing the function value as a variable.
DECLARE @CheckDate datetime = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; 
GO

如果此程序會對 SQL Server 的遠端執行個體進行變更，則無法回復這些變更。遠端程序不會參與交易。如需詳細資訊，請參閱＜在伺服器對伺服器的遠端預存程序中處理錯誤＞。

當正確的方法在 .NET Framework 中多載時，若要讓 Database Engine 參考正確的方法，EXTERNAL NAME 子句中所指定的方法必須具有下列特性：

• 宣告為靜態方法。

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

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

中繼資料

下表列出可讓您用來傳回預存程序之相關資訊的目錄檢視和動態管理檢視。

若要預估已編譯的程序大小，請使用下列效能監視器計數器：Cache Hit Ratio、Cache Pages 和 Cache Object Counts。如需詳細資訊，請參閱＜SQL Server 的 Plan Cache 物件＞。

安全性

權限

範例

基本語法

USE AdventureWorks2008R2;
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

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

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.uspMultipleResults 
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

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

傳遞參數

USE AdventureWorks2008R2;
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

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';

USE AdventureWorks2008R2;
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

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%';

USE AdventureWorks2008R2;
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

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)))+'.'

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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

使用預存程序修改資料

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
GO

EXEC HumanResources.Update_VacationHours 40;

錯誤處理

USE AdventureWorks2008R2;
GO

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS
SET NOCOUNT ON;
BEGIN TRY
   BEGIN TRANSACTION 
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;

/* Intentionally generate an error by reversing the order in which rows are deleted from the
   parent and child tables. This change does not cause an error when the procedure
   definition is altered, but produces an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )
AS

BEGIN TRY
   BEGIN TRANSACTION 
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.
   DELETE FROM Production.WorkOrder
   WHERE WorkOrderID = @WorkOrderID;
  
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
   DELETE FROM Production.WorkOrderRouting
   WHERE WorkOrderID = @WorkOrderID;
  
   COMMIT TRANSACTION

END TRY
BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;

DROP PROCEDURE Production.uspDeleteWorkOrder;

模糊化程序定義

USE AdventureWorks2008R2;
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 BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

EXEC sp_helptext 'HumanResources.uspEncryptThis';

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

強制程序重新編譯

USE AdventureWorks2008R2;
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.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

設定安全性內容

USE AdventureWorks2008R2;
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 'Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

請參閱

參考

概念

其他資源