1. 為什么使用存儲過程

應用程序通過T-SQL語句到服務器的過程是不安全的。

1） 數據不安全

2）每次提交SQL代碼都要經過語法編譯后在執行，影響應用程序的運行性能

3） 網絡流量大

2. 什么是存儲過程

存儲過程是SQL語句和控制語句的預編譯集合，保存在數據庫里，可由應用程序調用執行，而且允許用戶聲明變量、邏輯控制語句及其他強大的編程功能。保存在SQLServer中，通過名稱和參數執行，也可一返回結果。對于存儲過程我更傾向于把他理解成方法。它里面可以只有一條查詢語句，也可以包含一系列使用控制流的SQL語句。

3. 存儲過程的優點

1） 模塊化呈現設計

2） 執行速度快，效率高

3） 減少網絡流量

4） 具有良好的安全性

4. 存儲過程的分類

1）系統存儲過程

2）擴展存儲過程（屬于系統存儲過程的一種）

3）用戶自定義存儲過程

5. 系統存儲過程

它一般以"sp_"開頭，是由SQL Server創建、管理和使用，它存放在Resource數據庫中。類似C#語言類庫中的方法，暫時先不考慮它是如何編寫的，先了解常用的系統存儲過程及調用方法。

常見的系統存儲過程，見下一篇文章

調用方法：exec[ute] 存儲過程名 [參數值]

6. 常用的擴展存儲過程 xp_cmdshell

xp_cmdshell 它可以完成DOS命令下的一些操作。

exec xp_cmdshell DOS命令 [no_output]

說明 no_output是可選參數，表示設置執行DOS命令后是否輸出返回信息。

示例： exec xp_cmdshell 'mkdir D:/newdir' output

強調： 因為用戶可以通過xp_cmdshell對操作系統做一些操作，如果該存儲過程被黑客使用對操作系統做操作就麻煩了，所以通常會把xp_cmdshell 關閉掉：

方法一：

SQL Server 2008版本及以上， 通過數據庫右擊 選擇“方面” ，在下拉列表中選擇 “服務器安全‘ ， 下面的列表項中可以看到xmcmdshellEnable 設置。

SQL Server2005版本及以下，通過開始- SQLServer- 外圍設備查找

方法二：

關閉xp_cmdshell

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

開啟xp_cmdshell

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 0;

RECONFIGURE;

7. 用戶自定義存儲過程

語法：

create PRoc[edure] 存儲過程名

@參數1 數據類型 = 默認值 output,

……

@參數n 數據類型 = 默認值 output

as

<SQL 語句>

go

一個完成的存儲過程包含以下3部分：

1） 輸入參數、輸出參數

2） 在存儲過程中執行的T-SQL語句

3） 存儲過程的返回值

其中輸入參數允許有默認值。

刪除存儲過程

drop proc 存儲過程名

if exists (select * from sysobject where name = 存儲過程名)

drop proc 存儲過程名

go

8. 注意事項

存儲過程的聲明： 輸入參數可以有默認值，輸出參數也可以有默認值

create proc usp_name

@age int = 5,

　　　　　　@name varchar(10)

as

……

go

執行語句：

exec pr_name 18 , 'zm'

exec default , 'zm'

exec @name = 'zm'

說明： 為了調用方便，最好將有默認值的存儲過程參數列表放到最后。

帶輸出參數的存儲過程

create proc usp_name

@num1 int,

@sum int output

as

<SQL語句>

go

調用存儲過程

declare @sum int

exec usp_name 5, @sum output

注意， 調用帶有輸出參數的存儲過程參數后面必須帶output關鍵字

9. 處理存儲過程中的錯誤

raiserror （ {msg_id | msg_str} {, serverity, state } [with option [,……]]）

其中:

msg_id: 在sysmessage系統表中指定用戶定義錯誤信息

msg_str: 用戶定義的特定信息，最長為255個字符

serverity: 與特定信息相關聯，表示用戶定義的嚴重性級別。用戶可選用的級別是0~18。數字越大，表示越嚴重。

state : 表示錯誤的狀態， 1~255中的值

option: 錯誤的自定義選項，可以使一下任意一值

　　　　LOG: 在Microsoft SQl Server 數據庫引擎示例的錯誤日志和應用程序日志中記錄錯誤

NOWAIT:將消息立即發送給客戶端

　　 SETERROR:將@@error值和 ERROR_NUMBER 值設置為msg_id 或5000， 不用考慮嚴重級別。

例如： raiserror ('錯誤信息', 16,1)