1. 為什么使用存儲(chǔ)過程

應(yīng)用程序通過T-SQL語句到服務(wù)器的過程是不安全的。

1） 數(shù)據(jù)不安全

2）每次提交SQL代碼都要經(jīng)過語法編譯后在執(zhí)行，影響應(yīng)用程序的運(yùn)行性能

3） 網(wǎng)絡(luò)流量大

2. 什么是存儲(chǔ)過程

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

3. 存儲(chǔ)過程的優(yōu)點(diǎn)

1） 模塊化呈現(xiàn)設(shè)計(jì)

2） 執(zhí)行速度快，效率高

3） 減少網(wǎng)絡(luò)流量

4） 具有良好的安全性

4. 存儲(chǔ)過程的分類

1）系統(tǒng)存儲(chǔ)過程

2）擴(kuò)展存儲(chǔ)過程（屬于系統(tǒng)存儲(chǔ)過程的一種）

3）用戶自定義存儲(chǔ)過程

5. 系統(tǒng)存儲(chǔ)過程

它一般以"sp_"開頭，是由SQL Server創(chuàng)建、管理和使用，它存放在Resource數(shù)據(jù)庫中。類似C#語言類庫中的方法，暫時(shí)先不考慮它是如何編寫的，先了解常用的系統(tǒng)存儲(chǔ)過程及調(diào)用方法。

常見的系統(tǒng)存儲(chǔ)過程，見下一篇文章

調(diào)用方法：exec[ute] 存儲(chǔ)過程名 [參數(shù)值]

6. 常用的擴(kuò)展存儲(chǔ)過程 xp_cmdshell

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

exec xp_cmdshell DOS命令 [no_output]

說明 no_output是可選參數(shù)，表示設(shè)置執(zhí)行DOS命令后是否輸出返回信息。

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

強(qiáng)調(diào)： 因?yàn)橛脩艨梢酝ㄟ^xp_cmdshell對(duì)操作系統(tǒng)做一些操作，如果該存儲(chǔ)過程被黑客使用對(duì)操作系統(tǒng)做操作就麻煩了，所以通常會(huì)把xp_cmdshell 關(guān)閉掉：

方法一：

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

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

方法二：

關(guān)閉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. 用戶自定義存儲(chǔ)過程

語法：

create PRoc[edure] 存儲(chǔ)過程名

@參數(shù)1 數(shù)據(jù)類型 = 默認(rèn)值 output,

……

@參數(shù)n 數(shù)據(jù)類型 = 默認(rèn)值 output

as

<SQL 語句>

go

一個(gè)完成的存儲(chǔ)過程包含以下3部分：

1） 輸入?yún)?shù)、輸出參數(shù)

2） 在存儲(chǔ)過程中執(zhí)行的T-SQL語句

3） 存儲(chǔ)過程的返回值

其中輸入?yún)?shù)允許有默認(rèn)值。

刪除存儲(chǔ)過程

drop proc 存儲(chǔ)過程名

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

drop proc 存儲(chǔ)過程名

go

8. 注意事項(xiàng)

存儲(chǔ)過程的聲明： 輸入?yún)?shù)可以有默認(rèn)值，輸出參數(shù)也可以有默認(rèn)值

create proc usp_name

@age int = 5,

　　　　　　@name varchar(10)

as

……

go

執(zhí)行語句：

exec pr_name 18 , 'zm'

exec default , 'zm'

exec @name = 'zm'

說明： 為了調(diào)用方便，最好將有默認(rèn)值的存儲(chǔ)過程參數(shù)列表放到最后。

帶輸出參數(shù)的存儲(chǔ)過程

create proc usp_name

@num1 int,

@sum int output

as

<SQL語句>

go

調(diào)用存儲(chǔ)過程

declare @sum int

exec usp_name 5, @sum output

注意， 調(diào)用帶有輸出參數(shù)的存儲(chǔ)過程參數(shù)后面必須帶output關(guān)鍵字

9. 處理存儲(chǔ)過程中的錯(cuò)誤

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

其中:

msg_id: 在sysmessage系統(tǒng)表中指定用戶定義錯(cuò)誤信息

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

serverity: 與特定信息相關(guān)聯(lián)，表示用戶定義的嚴(yán)重性級(jí)別。用戶可選用的級(jí)別是0~18。數(shù)字越大，表示越嚴(yán)重。

state : 表示錯(cuò)誤的狀態(tài)， 1~255中的值

option: 錯(cuò)誤的自定義選項(xiàng)，可以使一下任意一值

　　　　LOG: 在Microsoft SQl Server 數(shù)據(jù)庫引擎示例的錯(cuò)誤日志和應(yīng)用程序日志中記錄錯(cuò)誤

NOWAIT:將消息立即發(fā)送給客戶端

　　 SETERROR:將@@error值和 ERROR_NUMBER 值設(shè)置為msg_id 或5000， 不用考慮嚴(yán)重級(jí)別。

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