1、設計存儲過程
幾乎任何可寫成批處理的 Transact-SQL 代碼都可用于創建存儲過程。
2、存儲過程的設計規則
存儲過程的設計規則包括：
- CREATE PROCEDURE 定義本身可包括除下列 CREATE 語句以外的任何數量和類型的 SQL 語句，存儲過程中的任意地方都不能使用下列語句：
| CREATE DEFAULT |
CREATE TRIGGER |
| CREATE PROCEDURE |
CREATE VIEW |
| CREATE RULE |
|
- 可在存儲過程中創建其它數據庫對象。可以引用在同一存儲過程中創建的對象，前提是在創建對象后再引用對象。
- 可以在存儲過程內引用臨時表。
- 如果在存儲過程內創建本地臨時表，則該臨時表僅為該存儲過程而存在；退出該存儲過程后，臨時表即會消失。
- 如果執行調用其它存儲過程的存儲過程，那么被調用存儲過程可以訪問由第一個存儲過程創建的、包括臨時表在內的所有對象。
- 如果執行在遠程 Microsoft® SQL Server™ 2000 實例上進行更改的遠程存儲過程，則不能回滾這些更改。遠程存儲過程不參與事務處理。
- 存儲過程中參數的最大數目為 2100。
- 存儲過程中局部變量的最大數目僅受可用內存的限制。
- 根據可用內存的不同，存儲過程的最大大小可達 128 MB。
3、限定存儲過程內的名稱
在存儲過程內部，如果用于諸如 SELECT 或 INSERT 這樣的語句的對象名沒有限定用戶，那么用戶將默認為該存儲過程的所有者。在存儲過程內部，如果創建存儲過程的用戶沒有限定 SELECT、INSERT、UPDATE 或 DELETE 語句中引用的表名，那么通過該存儲過程對這些表進行的訪問將默認地受到該過程的創建者權限的限制。
如果有其他用戶要使用存儲過程，則用于語句 ALTER TABLE、CREATE TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 和 DBCC 的對象名必須用該對象所有者的名稱限定。例如，Mary 擁有表 marytab，如果她希望其他用戶能夠執行使用該表的存儲過程，必須在該表用于上述某一條語句時對其表名進行限定。
此規則是必需的，因為運行存儲過程時將解析對象的名稱。如果未限定 marytab，而 John 試圖執行該過程，SQL Server 將查找 John 所擁有的名為 marytab 的表。
4、加密過程定義
如果要創建存儲過程，并且希望確保其他用戶無法查看該過程的定義，那么可以使用 WITH ENCRYPTION 子句。這樣，過程定義將以不可讀的形式存儲。
存儲過程一旦加密其定義即無法解密，任何人（包括存儲過程的所有者或系統管理員）都將無法查看存儲過程定義。
5、SET 語句選項
當 ODBC 應用程序與 SQL Server 連接時，服務器將自動設置會話的下列選項：
- SET QUOTED_IDENTIFIER ON
- SET TEXTSIZE 2147483647
- SET ANSI_DEFAULTS ON
- SET CURSOR_CLOSE_ON_COMMIT OFF
- SET IMPLICIT_TRANSACTIONS OFF
這些設置將提高 ODBC 應用程序的可移植性。由于基于 DB-Library 的應用程序通常不設置這些選項，所以應在上述所列 SET 選項打開和關閉的情況下都對存儲過程進行測試。這樣可確保存儲過程始終能正確工作，而不管特定的連接在喚醒調用該存儲過程時可能設置的選項。需要特別設置其中一個選項的存儲過程，應在開始該存儲過程時發出一條 SET 語句。此 SET 語句將只對該存儲過程的執行保持有效，當該存儲過程結束時，將恢復原設置。
示例
A. 創建使用參數的存儲過程
下例創建一個在 pubs 數據庫中很有用的存儲過程。給出一個作者的姓和名，該存儲過程將顯示該作者的每本書的標題和出版商。
CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
將出現一條說明該命令未返回任何數據也未返回任何行的消息，這表示已創建該存儲過程。
現在執行 au_info 存儲過程：
EXECUTE au_info Ringer, Anne
GO
下面是結果集：
| au_lname |
au_fname |
title |
pub_name |
| --------- |
--------- |
--------------------- |
---------------- |
| Ringer |
Anne |
The Gourmet Microwave |
Binnet & Hardley |
| Ringer |
Anne |
Is Anger the Enemy? |
New Moon Books |
(2 row(s) affected)
B. 創建使用參數默認值的存儲過程
下例創建一個存儲過程 pub_info2，該存儲過程顯示作為參數給出的出版商所出版的某本書的作者姓名。如果未提供出版商的名稱，該存儲過程將顯示由 Algodata Infosystems 出版的書籍的作者。
CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'
AS
SELECT au_lname, au_fname, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
JOIN titles t ON ta.title_id = t.title_id
JOIN publishers p ON t.pub_id = p.pub_id
WHERE @pubname = p.pub_name
執行未指定參數的 pub_info2：
EXECUTE pub_info2
GO
下面是結果集：
| au_lname |
au_fname |
pub_name |
| ---------------- |
---------------- |
-------------------- |
| Green |
Marjorie |
Algodata Infosystems |
| Bennet |
Abraham |
Algodata Infosystems |
| O'Leary |
Michael |
Algodata Infosystems |
| MacFeather |
Stearns |
Algodata Infosystems |
| Straight |
Dean |
Algodata Infosystems |
| Carson |
Cheryl |
Algodata Infosystems |
| Dull |
Ann |
Algodata Infosystems |
| Hunter |
Sheryl |
Algodata Infosystems |
| Locksley |
Charlene |
Algodata Infosystems |
(9 row(s) affected)
C. 執行用顯式值替代參數默認值的存儲過程
在下例中，存儲過程 showind2 的 @table 參數默認值是 titles。
CREATE PROC showind2 @table varchar(30) = 'titles'
AS
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table
列標題（例如，TABLE_NAME）可使結果更具可讀性。下面是該存儲過程顯示的 authors 表的情況：
EXECUTE showind2 authors
GO
| TABLE_NAME |
INDEX_NAME |
INDEX_ID |
| ---------- |
---------- |
---------- |
| authors |
UPKCL_auidind |
1 |
| authors |
aunmind |
2 |
(2 row(s) affected)
如果用戶未提供值，則 SQL Server 將使用默認表 titles：
EXECUTE showind2
GO
下面是結果集：
| TABLE_NAME |
INDEX_NAME |
INDEX_ID |
| ---------- |
---------- |
---------- |
| titles |
UPKCL_titleidind |
1 |
| titles |
titleind |
2 |
(2 row(s) affected)
D. 使用參數默認值 NULL 創建存儲過程
參數默認值可以是 NULL 值。在這種情況下，如果未提供參數，則 SQL Server 將根據存儲過程的其它語句執行存儲過程。不會顯示錯誤信息。
過程定義還可指定當不給出參數時要采取的其它某種措施。例如：
CREATE PROC showind3 @table varchar(30) = NULL
AS IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table
E. 使用包含通配符的參數默認值創建存儲過程
如果存儲過程將參數用于 LIKE 關鍵字，那么默認值可包括通配符（%、_、[] 和 [^]）。例如，可將 showind 修改為當不提供參數時顯示有關系統表的信息：
CREATE PROC showind4 @table varchar(30) = 'sys%'
AS SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name LIKE @table
在存儲過程 au_info 的下列變化形式中，兩個參數都有帶通配符的默認值：
CREATE PROC au_info2 @lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
如果執行 au_info2 時不指定參數，將顯示姓以字母 D 開頭的所有作者：
EXECUTE au_info2
GO
下面是結果集：
| au_lname |
au_fname |
title |
pub_name |
| -------- |
-------- |
--------------------- |
------------------- |
| Dull |
Ann |
Secrets of Silicon Val |
Algodata Infosystems |
| del Castillo |
Innes |
Silicon Val Gastrono |
Binnet & Hardley |
| DeFrance |
Michel |
The Gourmet Microwave |
Binnet & Hardley |
(3 row(s) affected)
下例在兩個參數的默認值已定義的情況下，省略了第二個參數，因此可找到姓為 Ringer 的所有作者的書和出版商：
EXECUTE au_info2 Ringer
GO
| au_lname |
au_fname |
title |
pub_name |
| --------- |
--------- |
---------------------- |
---------------- |
| Ringer |
Anne |
The Gourmet Microwave |
Binnet & Hardley |
| Ringer |
Anne |
Is Anger the Enemy? |
New Moon Books |
| Ringer |
Albert |
Is Anger the Enemy? |
New Moon Books |
| Ringer |
Albert |
Life Without Fear |
New Moon Books |
(4 row(s) affected)