先站在應(yīng)用程序的角度說說它們的不同。
1、 直接拼SQL
就像大家了解的那樣，直接拼SQL帶來了SQL注入攻擊，帶來了拼時些許的性能損失，但是拼不用添加SqlParameter，會少寫很多代碼——很多人喜歡直接拼，也許就因?yàn)檫@點(diǎn)。這種做法會把你拼好的SQL原樣直接發(fā)送到DB服務(wù)器去執(zhí)行。（注意類似”exec yourproc ‘param1’, 12”的語句不在此范疇，這是調(diào)用存儲過程的一種方式）
2、 參數(shù)化SQL
所謂的“參數(shù)化SQL”就是在應(yīng)用程序側(cè)設(shè)置SqlCommand.CommandText的時候使用參數(shù)（如：@param1），然后通過SqlCommand.Parameters.Add來設(shè)置這些參數(shù)的值。這種做法會把你準(zhǔn)備好的命令通過sp_executesql系統(tǒng)存儲過程來執(zhí)行。通過參數(shù)化SQL，和直接拼SQL相比，最直接的好處就是沒有SQL注入攻擊了。
3、 調(diào)用存儲過程
直接調(diào)用存儲過程其實(shí)和參數(shù)化SQL非常相似。唯一的本質(zhì)不同在于你發(fā)送到DB服務(wù)器的指令不再是sp_executesql，而是直接的存儲過程調(diào)用而已。
很多人非常非常厭惡在應(yīng)用程序中使用存儲過程，而寧愿使用拼SQL或者參數(shù)化SQL，理由是它們提供了更好的靈活性。
現(xiàn)在做設(shè)計(jì)，一般都是從上到下來，重心都在業(yè)務(wù)邏輯上。傳說中的領(lǐng)域模型設(shè)計(jì)完，測試用例都通過之后，才會考慮數(shù)據(jù)持久化方式。數(shù)據(jù)持久化是系統(tǒng)的一部分，但絕對不是最重要的部分，設(shè)計(jì)應(yīng)該圍繞業(yè)務(wù)邏輯開展，持久化應(yīng)該僅僅是個附件。至少，高層應(yīng)用應(yīng)該盡可能的不關(guān)心處于最底層的物理存儲結(jié)構(gòu)（如：表）和數(shù)據(jù)持久、反持久方式（是拼SQL還是存儲過程），所以用不用存儲過程根本不重要。很多人害怕存儲過程，其實(shí)是害怕存儲過程中包括業(yè)務(wù)邏輯——真實(shí)情況是，如果存儲過程中包含了業(yè)務(wù)邏輯，那一定最初需求分析不夠?qū)е掠美崛〔蛔悖瑢?dǎo)致測試用例覆蓋不夠，導(dǎo)致領(lǐng)域模型設(shè)計(jì)不充分，要不就是偷懶。
=====
站在DB角度討論它們的不同，主要從cpu、內(nèi)存方面來考慮，其他諸如安全性，msdn上都有，google也能拿到一堆資料，不再贅述。
首先是查詢計(jì)劃。
SQL編譯完一條SQL之后，會把它緩存起來（可以通過sys.syscacheobjects系統(tǒng)視圖查看），以后再有相同的查詢過來（注意sys.syscacheobjects視圖中的sql字段，和它存儲的東西完全一樣才能稱為“相同的查詢”），會直接使用緩存，而不再重新編譯。
? 存儲過程，只編譯一遍（如果沒有指定with recompile選項(xiàng)的話，如果指定了，根本就不會生成計(jì)劃緩存）。
? 參數(shù)化SQL，和存儲過程基本一樣，只要是相同的查詢，也都是只編譯一次，以后重用（當(dāng)然，指定了option(recompile)的除外）。這里不得不提.NET SqlClient組件的一個齷齪：如果你的參數(shù)中包含varchar或者char類型的參數(shù)，你在Parameters.Add的時候又沒有指定長度，它都會根據(jù)你實(shí)際傳入的字符串長度(假設(shè)是n)給你重新定義成nvarchar(n)。如：select * from mytable where col1 = @p1，你設(shè)置@p1為’123456’，實(shí)際傳到sql這邊的命令是：exec sp_executesql N'select * from mytable where col1 = @p1',N'@p1 nvarchar(6)',@p1=N'123456'。這樣，系統(tǒng)緩存中實(shí)際存儲的sql是：(@p1 nvarchar(6))select * from mytable where col1 = @p1。看到了吧？如果你的輸入?yún)?shù)變動比較多，那么看起來同樣的一條語句，會被編譯很多次，在緩存中存儲很多份。cpu和內(nèi)存都浪費(fèi)了。這也是在《寫有效率的SQL查詢IV》中建議的使用最強(qiáng)類型參數(shù)匹配的原因之一。
? 拼SQL。到這里不說大家也猜的出來，拼SQL要浪費(fèi)大量的cpu進(jìn)行編譯，浪費(fèi)大量緩存空間來存儲只用一次的查詢計(jì)劃。
服務(wù)器的物理內(nèi)存有限，SQLServer的緩存空間也有限。有限的空間應(yīng)該被充分利用。通過性能計(jì)數(shù)器SQL Server:Buffer Manager\Buffer Cache hit ratio來觀察緩存命中率。如果它小于百分之90，你就得研究研究了。關(guān)注一把諸如sys.dm_os_memory_cache_counters、sys.dm_os_memory_cache_entries、sys.dm_os_memory_cache_hash_tables、sys.syscacheobjects等視圖，基本可以確定問題出在哪兒。
cpu方面需要關(guān)注三個性能計(jì)數(shù)器：SQLServer:SQL Statistics\Batch Requests/Sec、SQLServer:SQL Statistics\ SQLCompilations/sec、SQLServer:SQL Statistics\ SQL Re-Compilations/sec。如果compilations數(shù)目超過batch請求數(shù)目的百分之10，或者recompilations數(shù)目超過compilations數(shù)目的百分之10，那基本可以說明cpu消耗了太多在編譯查詢計(jì)劃上面。