存儲過程天天用，關(guān)于使用存儲過程的sql語句的爭論也一直在，個人覺得使用存儲過程要好于用sql語句，整理了一些說明：

存儲過程是由一些SQL語句和控制語句組成的被封裝起來的過程，它駐留在數(shù)據(jù)庫中，可以被客戶應(yīng)用程序調(diào)用，也可以從另一個過程或觸發(fā)器調(diào)用。它的參數(shù)可以被傳遞和返回。與應(yīng)用程序中的函數(shù)過程類似，存儲過程可以通過名字來調(diào)用，而且它們同樣有輸入?yún)?shù)和輸出參數(shù)。

根據(jù)返回值類型的不同，我們可以將存儲過程分為三類：返回記錄集的存儲過程， 返回數(shù)值的存儲過程(也可以稱為標(biāo)量存儲過程)，以及行為存儲過程。顧名思義，返回記錄集的存儲過程的執(zhí)行結(jié)果是一個記錄集，典型的例子是從數(shù)據(jù)庫中檢索出符合某一個或幾個條件的記錄;返回數(shù)值的存儲過程執(zhí)行完以后返回一個值，例如在數(shù)據(jù)庫中執(zhí)行一個有返回值的函數(shù)或命令;最后，行為存儲過程僅僅是用來實現(xiàn)數(shù)據(jù)庫的某個功能，而沒有返回值，例如在數(shù)據(jù)庫中的更新和刪除操作。

使用存儲過程的好處

相對于直接使用SQL語句，在應(yīng)用程序中直接調(diào)用存儲過程有以下好處：

(1)減少網(wǎng)絡(luò)通信量。調(diào)用一個行數(shù)不多的存儲過程與直接調(diào)用SQL語句的網(wǎng)絡(luò)通信量可能不會有很大的差別，可是如果存儲過程包含上百行SQL語句，那么其性能絕對比一條一條的調(diào)用SQL語句要高得多。

(2)執(zhí)行速度更快。有兩個原因：首先，在存儲過程創(chuàng)建的時候，數(shù)據(jù)庫已經(jīng)對其進行了一次解析和優(yōu)化。其次，存儲過程一旦執(zhí)行，在內(nèi)存中就會保留一份這個存儲過程，這樣下次再執(zhí)行同樣的存儲過程時，可以從內(nèi)存中直接調(diào)用。

(3)更強的適應(yīng)性：由于存儲過程對數(shù)據(jù)庫的訪問是通過存儲過程來進行的，因此數(shù)據(jù)庫開發(fā)人員可以在不改動存儲過程接口的情況下對數(shù)據(jù)庫進行任何改動，而這些改動不會對應(yīng)用程序造成影響。

(4) 布式工作：應(yīng)用程序和數(shù)據(jù)庫的編碼工作可以分別獨立進行，而不會相互壓制。

msdn上面相關(guān)的說明

考慮使用存儲過程的理由

也許您曾經(jīng)在多處編寫過使用 SqlCommand 對象的 T-SQL，但卻從未考慮過是否有一個比將它并入數(shù)據(jù)訪問代碼更好的位置。由于應(yīng)用程序隨著時間的推移增添了一些功能，因此其內(nèi)部可能包含一些復(fù)雜的 T-SQL 過程代碼。存儲過程為封裝此代碼提供了一個替換位置。

大多數(shù)人可能對存儲過程已有所了解，但對于那些不了解存儲過程的人員而言，存儲過程是指一組作為單個代碼單元一起存儲于數(shù)據(jù)庫中的 T-SQL 語句。您可以使用輸入?yún)?shù)傳入運行時信息，并取回作為結(jié)果集或輸出參數(shù)的數(shù)據(jù)。存儲過程在首次運行時將被編譯。這將產(chǎn)生一個執(zhí)行計劃 - 實際上是 Microsoft SQL Server 為在存儲過程中獲取由 T-SQL 指定的結(jié)果而必須采取的步驟的記錄。然后，執(zhí)行計劃在內(nèi)存中得到緩存，以備以后使用。這樣會改善存儲過程的性能，因為 SQL Server 無需為確定如何處理代碼而重新分析它，而只需引用緩存的計劃即可。這個緩存的計劃一直可用，直到 SQL Server 重新啟動，或直到它由于使用率較低而溢出內(nèi)存。

性能

緩存的執(zhí)行計劃曾使存儲過程較之查詢更有性能優(yōu)勢。但對于 SQL Server 的幾個最新版本，執(zhí)行計劃已針對所有 T-SQL 批處理進行了緩存，而不管它們是否在存儲過程中。因此，基于此功能的性能已不再是存儲過程的賣點。任何使用靜態(tài)語法，且提交頻率足以阻止執(zhí)行計劃溢出內(nèi)存的 T-SQL 批處理將會獲得同樣的性能好處。“靜態(tài)”部分是關(guān)鍵;任何更改，即使像添加注釋這樣無關(guān)緊要的更改，也將導(dǎo)致無法與緩存的計劃相匹配，從而將無法重復(fù)使用計劃。

但是，當(dāng)存儲過程可以用于降低網(wǎng)絡(luò)流量時，它們?nèi)匀荒軌蛱峁┬阅芎锰?。您只需在網(wǎng)絡(luò)中發(fā)送 EXECUTE stored_proc_name 語句，而非整個 T-SQL 例程，這可以在復(fù)雜操作中廣泛使用。設(shè)計良好的存儲過程可以將客戶端與服務(wù)器之間的許多往返過程簡化為單個調(diào)用。

此外，使用存儲過程使您能夠增強對執(zhí)行計劃的重復(fù)使用，由此可以通過使用遠(yuǎn)程過程調(diào)用 (RPC) 處理服務(wù)器上的存儲過程而提高性能。使用 StoredProcedure 的 SqlCommand.CommandType 時，存儲過程通過 RPC 執(zhí)行。RPC 封裝參數(shù)和調(diào)用服務(wù)器端過程的方式使引擎能夠輕松地找到匹配的執(zhí)行計劃，并只需插入更新的參數(shù)值。

考慮使用存儲過程提高性能時，最后要考慮是否要充分利用 T-SQL 的優(yōu)點。請考慮要如何處理數(shù)據(jù)。 

是否要使用基于集合的操作，或執(zhí)行 T-SQL 中完全支持的其他操作？那么存儲過程就是一個選擇，而內(nèi)聯(lián)查詢也可以使用。

是否嘗試執(zhí)行基于行的操作，或復(fù)雜的字符串處理？那么可能要重新考慮在 T-SQL 中進行這種處理，這不包括使用存儲過程，至少要到 Yukon 發(fā)布并且公共語言運行庫 (CLR) 集成可用后，才能使用存儲過程。

可維護性和抽象

要考慮的另一個潛在優(yōu)勢是可維護性。理想情況下，數(shù)據(jù)庫架構(gòu)從不更改，業(yè)務(wù)規(guī)則不被修改，但在現(xiàn)實環(huán)境中，情況則完全不同。既然情況如此，那么如果可以修改存儲過程以包括新 X、Y 和 Z 表(為支持新的銷售活動而添加了這些表)中的數(shù)據(jù)，而不是在應(yīng)用程序代碼中的某個位置更改此信息，則維護對您來說可能比較容易。在存儲過程中更改此信息使得更新對應(yīng)用程序而言具有透明性 - 您仍然返回相同的銷售信息，即使存儲過程的內(nèi)部實現(xiàn)已經(jīng)更改。更新存儲過程通常比更改、測試以及重新部署程序集需要較少的時間和精力。

另外，通過抽象化實現(xiàn)并將此代碼保存在存儲過程中，任何需要訪問數(shù)據(jù)的應(yīng)用程序均可以獲取一致的數(shù)據(jù)。您無需在多個位置維護相同的代碼，用戶便可獲取一致的信息。

在存儲過程中存儲 T-SQL 的另一個可維護性優(yōu)點是更好的版本控制。您可以對創(chuàng)建和修改存儲過程的腳本進行版本控制，就像可以對任何其他源代碼模塊進行版本控制一樣。通過使用 Microsoft Visual SourceSafe 或某個其他源代碼控制工具，您可以輕松地恢復(fù)到或引用舊版本的存儲過程。

在使用存儲過程提高可維護性時應(yīng)值得注意的一點是，它們無法阻止您對架構(gòu)和規(guī)則進行所有可能的更改。如果更改范圍大到需要對輸入存儲過程的參數(shù)進行更改，或者要更改由其返回的數(shù)據(jù)，則您仍需要更新程序集中的代碼以添加參數(shù)、更新 GetValue() 調(diào)用，等等。

要注意的另一個問題是，由于存儲過程將應(yīng)用程序綁定到 SQL Server，因此使用存儲過程封裝業(yè)務(wù)邏輯將限制應(yīng)用程序的可移植性。如果應(yīng)用程序的可移植性在您的環(huán)境中非常重要，則將業(yè)務(wù)邏輯封裝在不特定于 RDBMS 的中間層中可能是一個更佳的選擇。

安全性

考慮使用存儲過程的最終原因是它們可用于增強安全性。

就管理用戶對信息的訪問而言，通過向用戶授予對存儲過程(而不是基礎(chǔ)表)的訪問權(quán)限，它們可以提供對特定數(shù)據(jù)的訪問。您可以將存儲過程看成是 SQL Server 視圖(如果您對它們熟悉的話)，除非存儲過程接受用戶的輸入以動態(tài)更改顯示的數(shù)據(jù)。

存儲過程還可以幫助您解決代碼安全問題。它們可以防止某些類型的 SQL 插入攻擊 - 主要是一些使用運算符(如 AND 或 OR)將命令附加到有效輸入?yún)?shù)值的攻擊。在應(yīng)用程序受到攻擊時，存儲過程還可以隱藏業(yè)務(wù)規(guī)則的實現(xiàn)。這對于將此類信息視為知識產(chǎn)權(quán)的公司非常重要。

另外，使用存儲過程使您可以使用 ADO.NET 中提供的 SqlParameter 類指定存儲過程參數(shù)的數(shù)據(jù)類型。這為驗證用戶提供的值類型(作為深層次防御性策略的一部分)提供了一個簡單方法。在縮小可接受用戶輸入的范圍方面，參數(shù)在內(nèi)聯(lián)查詢中與在存儲過程中一樣有用。

使用存儲過程增強安全性時值得注意的是，糟糕的安全性或編碼做法仍然會使您受到攻擊。對 SQL Server 角色創(chuàng)建和分配如果不加注意將導(dǎo)致人們訪問到不應(yīng)看到的數(shù)據(jù)。同時，如果認(rèn)為使用存儲過程便可防止所有 SQL 插入代碼攻擊(例如，將數(shù)據(jù)操作語言 (DML) 附加到輸入?yún)?shù))，后果將是一樣的。

另外，無論 T-SQL 位于代碼還是位于存儲過程中，使用參數(shù)進行數(shù)據(jù)類型驗證都不是萬無一失的。所有用戶提供的數(shù)據(jù)(尤其是文本數(shù)據(jù))在傳遞到數(shù)據(jù)庫之前都應(yīng)受到附加的驗證。

存儲過程對我是否適用？

或許適合吧。讓我們概括一下它們的優(yōu)點：

通過降低網(wǎng)絡(luò)流量提高性能

提供單點維護

抽象化業(yè)務(wù)規(guī)則，以確保一致性和安全性

通過將某些形式的攻擊降至最低，以增強安全性

支持執(zhí)行計劃重復(fù)使用

如果您的環(huán)境允許利用存儲過程提供的好處(如上所述)，強烈建議使用它們。對于改進數(shù)據(jù)在環(huán)境中的處理方式而言，它們提供了一個很好的工具。另一方面，如果您的環(huán)境中存在可移植性、大量使用非 T-SQL 友好的進程或者不穩(wěn)定的數(shù)據(jù)庫架構(gòu)等削弱這些優(yōu)點的因素，則您可能要考慮其他方法。

另一個要注意的事項是機構(gòu)內(nèi)部所擁有的 T-SQL 專業(yè)人員的數(shù)量。您有足夠的 T-SQL 知識嗎？您愿意學(xué)習(xí)嗎？或者，您有 DBA 或合適的人員幫您編寫存儲過程嗎？掌握的 T-SQL 知識越多，存儲過程就會越好，維護它們就會越容易。例如，T-SQL 主要用于基于集合的操作，而不是基于行的操作。依賴于光標(biāo)(因為它們向您提示數(shù)據(jù)集)將導(dǎo)致性能降低。如果您不太了解 T-SQL，請將本文作為一次學(xué)習(xí)機會。無論您將它用在何處，本文介紹的知識都將改善您的代碼。

因此，如果您認(rèn)為存儲過程會為應(yīng)用程序增添特殊的效果，請繼續(xù)閱讀本文。我們將回顧一些簡化存儲過程使用的工具，并了解一些創(chuàng)建存儲過程的最佳做法。

注意事項

如果要開始創(chuàng)建與應(yīng)用程序一起使用的存儲過程，應(yīng)記住下面這些提示，以便兩者正常運行并良好地配合工作。

使用 SET NOCOUNT ON

默認(rèn)情況下，存儲過程將返回過程中每個語句影響的行數(shù)。如果不需要在應(yīng)用程序中使用該信息(大多數(shù)應(yīng)用程序并不需要)，請在存儲過程中使用 SET NOCOUNT ON 語句以終止該行為。根據(jù)存儲過程中包含的影響行的語句的數(shù)量，這將刪除客戶端和服務(wù)器之間的一個或多個往返過程。盡管這不是大問題，但它可以為高流量應(yīng)用程序的性能產(chǎn)生負(fù)面影響。

create procedure test_MyStoredProc @param1 intasset nocount on

不要使用 sp_ prefix

sp_ prefix 是為系統(tǒng)存儲過程保留的。數(shù)據(jù)庫引擎將始終首先在主數(shù)據(jù)庫中查找具有此前綴的存儲過程。這意味著當(dāng)引擎首先檢查主數(shù)據(jù)庫，然后檢查存儲過程實際所在的數(shù)據(jù)庫時，將需要較長的時間才能完成檢查過程。而且，如果碰巧存在一個名稱相同的系統(tǒng)存儲過程，則您的過程根本不會得到處理。

盡量少用可選參數(shù)

在頻繁使用可選參數(shù)之前，請仔細(xì)考慮。通過執(zhí)行額外的工作會很輕易地影響性能，而根據(jù)為任意指定執(zhí)行輸入的參數(shù)集合，這些工作時不需要的。您可以通過對每種可能的參數(shù)組合使用條件編碼來解決此問題，但這相當(dāng)費時并會增大出錯的幾率。

在可能的情況下使用 OUTPUT 參數(shù)

通過使用 OUTPUT 參數(shù)返回標(biāo)量數(shù)據(jù)，可以略微提高速度并節(jié)省少量的處理功率。在應(yīng)用程序需要返回單個值的情況下，請嘗試此方法，而不要將結(jié)果集具體化。在適當(dāng)?shù)那闆r下，也可以使用 OUTPUT 參數(shù)返回光標(biāo)，但是我們將在后續(xù)文章中介紹光標(biāo)處理與基于集合的處理在理論上的分歧。

提供返回值

使用存儲過程的返回值，將處理狀態(tài)信息返回給進行調(diào)用的應(yīng)用程序。在您的開發(fā)組中，將一組返回值及其含義標(biāo)準(zhǔn)化，并一致地使用這些值。這會使得處理調(diào)用應(yīng)用程序中的錯誤更加容易，并向最終用戶提供有關(guān)問題的有用信息。

首先使用 DDL，然后使用 DML

將 DML 語句放在數(shù)據(jù)定義語言 (DDL) 語句之后執(zhí)行(此時 DML 將引用 DDL 修改的任意對象)時，SQL Server 將重新編譯存儲過程。出現(xiàn)這種情況，是由于為了給 DML 創(chuàng)建計劃，SQL Server 需要考慮由 DDL 對該對象所作的更改。如果留意存儲過程開頭的所有 DDL，則它只需重新編譯一次。如果將 DDL 和 DML 語句混合使用，則將強制存儲過程多次進行重新編譯，這將對性能造成負(fù)面影響。

始終使用注釋

您可能不會始終維護此代碼。但其他人員將來可能想要了解它的用途。'Nuff 曾經(jīng)這樣說。