共用方式為


建立預存程序 (Database Engine)

您可以使用 CREATE PROCEDURE Transact-SQL 陳述式來建立預存程序。

建立預存程序前,請仔細考慮:

  • CREATE PROCEDURE 陳述式不能與其他 SQL 陳述式合併在單一批次處理中。

  • 若要建立程序,您必須擁有資料庫的 CREATE PROCEDURE 權限以及建立程序之結構描述的 ALTER 權限。對於 CLR 預存程序,您必須擁有在 <method_specifier> 中參考的組件,或是擁有該組件的 REFERENCES 權限。

  • 預存程序是結構描述範圍的物件,而且其名稱必須依照識別碼的規則。

  • 您只可以在目前資料庫中建立預存程序。

建立預存程序時應該指定:

  • 傳給呼叫程序或批次處理的任何輸入參數及輸出參數。

  • 在資料庫中執行作業的程式陳述式,包括呼叫其他程序。

  • 傳回呼叫程序或批次處理的狀態值,以指示成功或失敗 (及失敗原因)。

  • 任何需要擷取和處理潛在錯誤的處理陳述式之錯誤。

    錯誤處理函數,例如 ERROR_LINE 與 ERROR_PROCEDURE,可在預存程序中指定。如需詳細資訊,請參閱使用 Transact-SQL 中的 TRY...CATCH

命名預存程序

建議您不要使用 sp_ 作為前置詞,來建立任何預存程序。SQL Server 會使用 sp_ 前置詞來指定系統預存程序。您選擇的名稱可能與某些未來的系統程序產生衝突。如果您的應用程式使用非資料結構合格的名稱參照,而您擁有的程序名稱和系統程序名稱相衝突,您的應用程式就會中斷,因為名稱會聯結到系統程序而非您的程序。

與系統預存程序的名稱相同、非限定或是在 dbo 結構描述中的使用者自訂預存程序,將永遠不會執行,反而會永遠執行系統預存程序。下列範例示範此行為。

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

使用明確的結構描述限定詞 (qualifier) 也可提供一些效能優點。如果 Database Engine 不必搜尋多個結構描述即可找到程序,則名稱解析會稍微快一點。如需詳細資訊,請參閱<執行預存程序>。

暫存預存程序

在程序名稱前使用 # 和 ## 前置詞,就可以建立私用和全域的暫時性預存程序 (類似暫存資料表)。# 代表本機的暫時性預存程序;## 代表全域的暫時性預存程序。在 SQL Server 關機後這些程序就不存在。

暫時性預存程序有助於連接到 SQL Server 的舊版,因為舊版不支援重覆使用 Transact-SQL 陳述式或批次的執行計畫。連接到 SQL Server 2000 的應用程式應使用 sp_executesql 系統預存程序,而非暫時性預存程序。只有建立本機暫時性程序的連線可以執行它,而且關閉連線時就會自動刪除該程序。

任何連線都可以執行全域的暫時性預存程序。全域的暫時性預存程序會一直存在,直到關閉建立程序的使用者所用的連線,以及任何其他連線目前執行中的程序版本完成為止。一旦關閉建立程序所用的連線,就不再允許執行全域的暫時性預存程序。而只允許已開始執行預存程序的連線繼續完成。

如果在 tempdb 資料庫中直接建立未在前面加上 # 或 ## 的預存程序,在關閉 SQL Server 時,將會自動刪除預存程序,因為每次啟動 SQL Server 時就會重新建立 tempdb。在 tempdb 中直接建立的程序,在結束建立的連接後仍然會存在。

[!附註]

過度使用暫時性預存程序將可能造成 tempdb 中系統資料表的競爭,並進而影響效能。建議改用 sp_executesqlsp_executesql 並不會在系統資料表中儲存資料,因此可避免問題。

CLR 預存程序無法建立成暫時性預存程序。

範例

A. 使用有複雜 SELECT 的簡單程序

下列預存程序會從檢視傳回所有的員工 (附有姓和名)、他們的職稱,以及他們的部門名稱。這個預存程序沒有使用任何參數。

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

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

您可以利用下列方式執行 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 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

您可以利用多種組合執行 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. 使用 OUTPUT 參數

下列範例會建立 uspGetList 預存程序,它會傳回價格未超過指定金額的產品清單。此範例顯示使用多個 SELECT 陳述式和多個 OUTPUT 參數。OUTPUT 參數可以讓外部程序、批次或一個以上的 Transact-SQL 陳述式在程序執行過程中存取某一值集。

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

執行 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.