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

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

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

使用存儲過程的好處

相對于直接使用SQL語句，在應用程序中直接調用存儲過程有以下好處：

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

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

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

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

msdn上面相關的說明

考慮使用存儲過程的理由

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

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

性能

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

但是，當存儲過程可以用于降低網絡流量時，它們仍然能夠提供性能好處。您只需在網絡中發(fā)送 EXECUTE stored_proc_name 語句，而非整個 T-SQL 例程，這可以在復雜操作中廣泛使用。設計良好的存儲過程可以將客戶端與服務器之間的許多往返過程簡化為單個調用。

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

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

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

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

可維護性和抽象

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

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

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

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

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

安全性

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

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

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

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

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

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

存儲過程對我是否適用？

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

通過降低網絡流量提高性能

提供單點維護

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

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

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

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

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

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

注意事項

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

使用 SET NOCOUNT ON

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

create procedure test_MyStoredProc @param1 intasset nocount on

不要使用 sp_ prefix

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

盡量少用可選參數

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

在可能的情況下使用 OUTPUT 參數

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

提供返回值

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

首先使用 DDL，然后使用 DML

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

始終使用注釋

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