設計存儲過程

　　幾乎任何可寫成批處理的 TransactSQL 代碼都可用於創建存儲過程
　　
　　存儲過程的設計規則
　　存儲過程的設計規則包括
　　
　　CREATE PROCEDURE 定義本身可包括除下列 CREATE 語句以外的任何數量和類型的 SQL 語句存儲過程中的任意地方都不能使用下列語句
　　　
　　可在存儲過程中創建其它數據庫對象可以引用在同一存儲過程中創建的對象前提是在創建對象後再引用對象
　　
　　
　　可以在存儲過程內引用臨時表
　　
　　
　　如果在存儲過程內創建本地臨時表則該臨時表僅為該存儲過程而存在退出該存儲過程後臨時表即會消失
　　
　　
　　如果執行調用其它存儲過程的存儲過程那麼被調用存儲過程可以訪問由第一個存儲過程創建的包括臨時表在內的所有對象
　　
　　
　　如果執行在遠程 Microsoft® SQL Server&#; 實例上進行更改的遠程存儲過程則不能回滾這些更改遠程存儲過程不參與事務處理
　　
　　
　　存儲過程中參數的最大數目為
　　
　　
　　存儲過程中局部變量的最大數目僅受可用內存的限制
　　
　　
　　根據可用內存的不同存儲過程的最大大小可達 MB
　　有關創建存儲過程的規則的更多信息請參見 CREATE PROCEDURE
　　
　　限定存儲過程內的名稱
　　在存儲過程內部如果用於諸如 SELECT 或 INSERT 這樣的語句的對象名沒有限定用戶那麼用戶將默認為該存儲過程的所有者在存儲過程內部如果創建存儲過程的用戶沒有限定 SELECTINSERTUPDATE 或 DELETE 語句中引用的表名那麼通過該存儲過程對這些表進行的訪問將默認地受到該過程的創建者權限的限制
　　
　　如果有其他用戶要使用存儲過程則用於語句 ALTER TABLECREATE TABLEDROP TABLETRUNCATE TABLECREATE INDEXDROP INDEXUPDATE STATISTICS 和 DBCC 的對象名必須用該對象所有者的名稱限定例如Mary 擁有表 marytab如果她希望其他用戶能夠執行使用該表的存儲過程必須在該表用於上述某一條語句時對其表名進行限定
　　
　　此規則是必需的因為運行存儲過程時將解析對象的名稱如果未限定 marytab而 John 試圖執行該過程SQL Server 將查找 John 所擁有的名為 marytab 的表
　　
　　加密過程定義
　　如果要創建存儲過程並且希望確保其他用戶無法查看該過程的定義那麼可以使用 WITH ENCRYPTION 子句這樣過程定義將以不可讀的形式存儲
　　
　　存儲過程一旦加密其定義即無法解密任何人（包括存儲過程的所有者或系統管理員）都將無法查看存儲過程定義
　　
　　SET 語句選項
　　當 ODBC 應用程序與 SQL Server 連接時服務器將自動設置會話的下列選項
　　
　　SET QUOTED_IDENTIFIER ON
　　
　　
　　SET TEXTSIZE
　　
　　
　　SET ANSI_DEFAULTS ON
　　
　　
　　SET CURSOR_CLOSE_ON_COMMIT OFF
　　
　　
　　SET IMPLICIT_TRANSACTIONS OFF
　　這些設置將提高 ODBC 應用程序的可移植性由於基於 DBLibrary 的應用程序通常不設置這些選項所以應在上述所列 SET 選項打開和關閉的情況下都對存儲過程進行測試這樣可確保存儲過程始終能正確工作而不管特定的連接在喚醒調用該存儲過程時可能設置的選項需要特別設置其中一個選項的存儲過程應在開始該存儲過程時發出一條 SET 語句此 SET 語句將只對該存儲過程的執行保持有效當該存儲過程結束時將恢復原設置
　　
　　示例
　　A 創建使用參數的存儲過程
　　下例創建一個在 pubs 數據庫中很有用的存儲過程給出一個作者的姓和名該存儲過程將顯示該作者的每本書的標題和出版商
　　
　　CREATE PROC au_info @lastname varchar() @firstname varchar()
　　AS
　　SELECT au_lname au_fname title pub_name
　　FROM authors INNER JOIN titleauthor ON authorsau_id = titleauthorau_id
　　JOIN titles ON titleauthortitle_id = titlestitle_id
　　JOIN publishers ON titlespub_id = publisherspub_id
　　WHERE au_fname = @firstname
　　AND au_lname = @lastname
　　GO
　　
　　將出現一條說明該命令未返回任何數據也未返回任何行的消息這表示已創建該存儲過程
　　
　　現在執行 au_info 存儲過程
　　
　　EXECUTE au_info Ringer Anne
　　GO
　　
　　下面是結果集
　　　
　　
　　( row(s) affected)
　　
　　B 創建使用參數默認值的存儲過程
　　下例創建一個存儲過程 pub_info該存儲過程顯示作為參數給出的出版商所出版的某本書的作者姓名如果未提供出版商的名稱該存儲過程將顯示由 Algodata Infosystems 出版的書籍的作者
　　
　　CREATE PROC pub_info @pubname varchar() = Algodata Infosystems
　　AS
　　SELECT au_lname au_fname pub_name
　　FROM authors a INNER JOIN titleauthor ta ON aau_id = taau_id
　　JOIN titles t ON tatitle_id = ttitle_id
　　JOIN publishers p ON tpub_id = ppub_id
　　WHERE @pubname = ppub_name
　　
　　執行未指定參數的 pub_info
　　
　　EXECUTE pub_info
　　GO
　　
　　下面是結果集
　　　
　　( row(s) affected)
　　
　　C 執行用顯式值替代參數默認值的存儲過程
　　在下例中存儲過程 showind 的 @table 參數默認值是 titles
　　
　　CREATE PROC showind @table varchar() = titles
　　AS
　　SELECT TABLE_NAME = sysobjectsname
　　INDEX_NAME = sysindexesname INDEX_ID = indid
　　FROM sysindexes INNER JOIN sysobjects ON sysobjectsid = sysindexesid
　　WHERE sysobjectsname = @table
　　
　　列標題（例如TABLE_NAME）可使結果更具可讀性下面是該存儲過程顯示的 authors 表的情況
　　
　　EXECUTE showind authors
　　GO
　　
　　　
　　( row(s) affected)
　　
　　如果用戶未提供值則 SQL Server 將使用默認表 titles
　　
　　EXECUTE showind
　　GO
　　
　　下面是結果集
　　　
　　( row(s) affected)
　　
　　D 使用參數默認值 NULL 創建存儲過程
　　參數默認值可以是 NULL 值在這種情況下如果未提供參數則 SQL Server 將根據存儲過程的其它語句執行存儲過程不會顯示錯誤信息
　　
　　過程定義還可指定當不給出參數時要采取的其它某種措施例如
　　
　　CREATE PROC showind @table varchar() = NULL
　　AS IF @table IS NULL
　　PRINT Give a table name
　　ELSE
　　SELECT TABLE_NAME = sysobjectsname
　　INDEX_NAME = sysindexesname INDEX_ID = indid
　　FROM sysindexes INNER JOIN sysobjects
　　ON sysobjectsid = sysindexesid
　　WHERE sysobjectsname = @table
　　
　　E 使用包含通配符的參數默認值創建存儲過程
　　如果存儲過程將參數用於 LIKE 關鍵字那麼默認值可包括通配符（%_[] 和 [^]）例如可將 showind 修改為當不提供參數時顯示有關系統表的信息
　　
　　CREATE PROC showind @table varchar() = sys%
　　AS SELECT TABLE_NAME = sysobjectsname
　　INDEX_NAME = sysindexesname INDEX_ID = indid
　　FROM sysindexes INNER JOIN sysobjects
　　ON sysobjectsid = sysindexesid
　　WHERE sysobjectsname LIKE @table
　　
　　在存儲過程 au_info 的下列變化形式中兩個參數都有帶通配符的默認值
　　
　　CREATE PROC au_info @lastname varchar() = D%
　　@firstname varchar() = %
　　AS
　　SELECT au_lname au_fname title pub_name
　　FROM authors INNER JOIN titleauthor ON authorsau_id = titleauthorau_id
　　JOIN titles ON titleauthortitle_id = titlestitle_id
　　JOIN publishers ON titlespub_id = publisherspub_id
　　WHERE au_fname LIKE @firstname
　　AND au_lname LIKE @lastname
　　
　　如果執行 au_info 時不指定參數將顯示姓以字母 D 開頭的所有作者
　　
　　EXECUTE au_info
　　GO
　　
　　下面是結果集
　　　
　　( row(s) affected)
　　
　　下例在兩個參數的默認值已定義的情況下省略了第二個參數因此可找到姓為 Ringer 的所有作者的書和出版商
　　
　　EXECUTE au_info Ringer
　　GO
　　
　　( row(s) affected)

From:http://tw.wingwit.com/Article/os/xtgl/201311/9335.html