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