建立預存程序 (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_executesql。sp_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.