文出處： http://www.cnblogs.com/wy123/p/5958047.html

最近發現還有不少做開發的小伙伴，在寫存儲過程的時候，在參考已有的不同的寫法時，往往很迷茫，

不知道各種寫法孰優孰劣，該選用那種寫法，以及各種寫法優缺點，本文以一個簡單的查詢存儲過程為例，簡單說一下各種寫法的區別，以及該用那種寫法

專業DBA以及熟悉數據庫的同學請無視。

廢話不多，上代碼說明，先造一個測試表待用，簡單說明一下這個表的情況

類似訂單表，訂單表有訂單ID，客戶ID，訂單創建時間等，查詢條件是常用的訂單ID，客戶ID，以及訂單創建時間

create table SaleOrder

(

id 　　　　　　int identity(1,1),

OrderNumber int　　　　　　　　 ,

CustomerId varchar(20) ,

OrderDate datetime ,

Remark varchar(200)

)

GOdeclare @i int=0while @i<100000begin

insert into SaleOrder values (@i,CONCAT('C',cast(RAND()*1000 as int)),GETDATE()-RAND()*100,NEWID()) set @i=@i+1endcreate index idx_OrderNumber on SaleOrder(OrderNumber)create index idx_CustomerId on SaleOrder(CustomerId)create index idx_OrderDate on SaleOrder(OrderDate)

生成的測試數據大概就是這個樣子的

下面演示說明幾種常見的寫法以及每種寫法潛在的問題

第一種常見的寫法:拼湊字符串，用EXEC的方式執行這個拼湊出來的字符串，不推薦

create proc pr_getOrederInfo_1

( @p_OrderNumber int　　　　　　, @p_CustomerId varchar(20)　, @p_OrderDateBegin datetime　　 , @p_OrderDateEnd datetime)asbegin

set nocount on; declare @strSql nvarchar(max); set @strSql='SELECT [id]

　　 ,[OrderNumber]

　　 ,[CustomerId]

　　 ,[OrderDate]

　　 ,[Remark]

FROM [dbo].[SaleOrder] where 1=1 '; /*

這種寫法的特點在于將查詢SQL拼湊成一個字符串，最后以EXEC的方式執行這個SQL字符串 */

if(@p_OrderNumber is not null) set @strSql=@strSql + ' and OrderNumber=' + @p_OrderNumber

if(@p_CustomerId is not null) set @strSql=@strSql + ' and CustomerId='+ ''''+ @p_CustomerId + ''''

if(@p_OrderDateBegin is not null) set @strSql=@strSql + ' and OrderDate >=' + '''' + cast(@p_OrderDateBegin as varchar(10)) + ''''

if(@p_OrderDateEnd is not null) set @strSql=@strSql + ' and OrderDate <=' + '''' + cast(@p_OrderDateEnd as varchar(10)) + ''''

print @strSql

exec(@strSql);end

假如我們查詢CustomerId為88，在2016-10-1至2016-10-3這段時間內的訂單信息，如下，帶入參數執行

exec pr_getOrederInfo_1 @p_OrderNumber=null　　　　 , @p_CustomerId='C88'　　　　 , @p_OrderDateBegin='2016-10-1' , @p_OrderDateEnd='2016-10-3'

首先說明，這種方式執行查詢是完全沒有問題的如下截圖，結果也查出來了（當然結果也是沒問題的）

我們把執行的SQL打印出來，執行的SQL語句本身就是就是存儲過程中拼湊出來的字符串，這么一個查詢SQL字符串

SELECT [id]

,[OrderNumber]

,[CustomerId]

,[OrderDate]

,[Remark]FROM [dbo].[SaleOrder] where 1=1

and CustomerId='C88'

and OrderDate >='2016-10-1'

and OrderDate <='2016-10-3'

那么這種存儲過程的有什么問題，或者直接一點說，這種方式有什么不好的地方

其一，繞不過轉移符（以及注入問題）

在拼湊字符串時，把所有的參數都當成字符串處理，當查詢條件本身包含特殊字符的時候，比如 ' 符號，

或者其他需要轉義的字符???，你拼湊的SQL就被打斷了

舉個不恰當的例子，比如字符串中 @p_CustomerId中包含 ' 符號，直接就把你拼SQL的節湊給打亂了

拼湊的SQL就變成了這個樣子了，語法就不通過，更別提執行

SELECT [id]

,[OrderNumber]

,[CustomerId]

,[OrderDate]

,[Remark]

FROM [dbo].[SaleOrder]

where 1=1 and CustomerId='C'88'

一方面需要處理轉移符，另一方面需要要防止SQL注入

其二，參數不同就必須重新編譯

這種拼湊SQL的方式，如果每次查詢的參數不同，拼湊出來的SQL字符串也不一樣，

如果熟悉SQL Server的同學一定知道，只要你執行的SQL文本不一樣，

比如

第一次是執行查詢 *** where CustomerId='C88' ，

第二次是執行查詢 *** where CustomerId='C99' ，因為兩次執行的SQL文本不同

每次執行之前必然需要對其進行編譯，編譯的話就需要CPU，內存資源

如果存在大批量的SQL編譯，無疑要消耗更多的CPU資源（當然需要內存資源）

第二種常見的寫法:對所有查詢條件用OR的方式加在where條件中，非常不推薦

create proc pr_getOrederInfo_2

( @p_OrderNumber int　　　　　 , @p_CustomerId varchar(20) , @p_OrderDateBegin datetime　　 , @p_OrderDateEnd datetime)asbegin

set nocount on; declare @strSql nvarchar(max); SELECT [id]

,[OrderNumber]

,[CustomerId]

,[OrderDate]

,[Remark]

FROM [dbo].[SaleOrder]

where 1=1

and (@p_OrderNumber is null or OrderNumber=@p_OrderNumber) and (@p_CustomerId is null or CustomerId=@p_CustomerId) /*

這是另外一種類似的奇葩的寫法,下面會重點關注

and OrderNumber=ISNULL( @p_OrderNumber,OrderNumber)

and CustomerId=ISNULL( @p_CustomerId,CustomerId) */

and (@p_OrderDateBegin is null or OrderDate >=@p_OrderDateBegin) and (@p_OrderDateEnd is null or OrderDate <=@p_OrderDateEnd)

end

首先看這種方式的執行結果，帶入同樣的參數，跟上面的結果一樣，查詢（結果）本身是沒有任何問題的

這種寫法寫起來避免了拼湊字符串的處理，看起來很簡潔，寫起來也很快，稀里嘩啦一個存儲過程就寫好了，

發布到生產環境之后就相當于埋了一顆雷，隨時引爆。

因為一條低效而又頻繁執行的SQL，拖垮一臺服務器也是司空見慣

但是呢，問題非常多，也非常非常不推薦，甚至比第一種方式更糟糕。

分析一下這種處理方式的邏輯：

這種處理方式，因為不確定查詢的時候到底有沒有傳入參數，也就數說不能確定某一個查詢條件是否生效，

于是就采用類似　and (@p_OrderNumber is null or OrderNumber=@p_OrderNumber)這種方式，來處理參數，

這樣的話

如果@p_OrderNumber為null，or的前者（@p_OrderNumber is null）成立，后者不成立，查詢條件不生效

如果@p_OrderNumber為非null，or的后者（OrderNumber=@p_OrderNumber）成立而前者不成立，查詢條件生效

總之來說，不管參數是否為空，都可以有效地拼湊到查詢條件中去。

避免了拼SQL字符串，既做到讓參數非空的時候生效，有做到參數為空的時候不生效，看起來不錯，是真的嗎？

那么這種存儲過程的有什么問題？

1，可能會抑制索引的情況

為什么說可能會抑制到索引的時候？上面提到過，SQL在執行之前是需要編譯的，

因為在編譯的時候并不知道查詢條件是否傳入了值，有可能為null，有可能是一個具體的值

SQL Server為了保險起見，采用了全表掃描的方式，舉個簡單的例子

如果我直接帶入CustomerId=‘C88’，再來看執行計劃，結果跟上面一樣，但是執行計劃是完全不一樣的，這就是所謂的抑制到索引的使用。

2，非常非常致命的邏輯錯誤

/*

這是另外一種類似的奇葩的寫法,需要重點關注，真的就能滿足“不管參數是否為空都滿足”

and OrderNumber=ISNULL( @p_OrderNumber,OrderNumber)

and CustomerId=ISNULL( @p_CustomerId,CustomerId)

*/

對于如下這種寫法：OrderNumber=ISNULL( @p_OrderNumber,OrderNumber)，

一部分人非常推崇，認為這種方式簡單、清晰，我也是醉了，有可能產生非常嚴重的邏輯錯誤

如果參數為null,就轉換成這種語義 where 1=1 and OrderNumber=OrderNumber

目的是查詢參數為null,查詢條件不生效,讓這個查詢條件恒成立，恒成立嗎，不一定，某些情況下就會有嚴重的語義錯誤

博主發現這個問題也是因為某些實際系統中的bug，折騰了好久才發現這個嚴重的邏輯錯誤 http://www.cnblogs.com/wy123/p/5580821.html

對于這種寫法，

不管是第一點說的抑制索引的問題，數據量大的時候是非常嚴重的，上述寫法會造成全表掃描，有索引頁用不上，至于全表掃描的壞處就不說了

還是第二點說的造成的邏輯錯誤，都是非常致命的

所以這種方式是最最不推薦的。

第三種常見的寫法：參數化SQL，推薦

create proc pr_getOrederInfo_3

( @p_OrderNumber int　　　　　 , @p_CustomerId varchar(20) , @p_OrderDateBegin datetime　　 , @p_OrderDateEnd datetime)asbegin

set nocount on;

DECLARE @Parm NVARCHAR(MAX)=N'',

　　 @sqlcommand NVARCHAR(MAX)=N''

SET @sqlcommand='SELECT [id]

,[OrderNumber]

,[CustomerId]

,[OrderDate]

,[Remark]

FROM [dbo].[SaleOrder]

where 1=1 '

IF(@p_OrderNumber IS NOT NULL) SET @sqlcommand=CONCAT(@sqlcommand,' AND OrderNumber=@p_OrderNumber') IF(@p_CustomerId IS NOT NULL) SET @sqlcommand=CONCAT(@sqlcommand,' AND CustomerId=@p_CustomerId') IF(@p_OrderDateBegin IS NOT NULL) SET @sqlcommand=CONCAT(@sqlcommand,' AND OrderDate>=@p_OrderDateBegin ') IF(@p_OrderDateEnd IS NOT NULL) SET @sqlcommand=CONCAT(@sqlcommand,' AND OrderDate<=@p_OrderDateEnd ') SET @Parm='@p_OrderNumber int,

@p_CustomerId varchar(20),

@p_OrderDateBegin datetime,

@p_OrderDateEnd datetime '

PRINT @sqlcommand

EXEC sp_executesql @sqlcommand,@Parm, @p_OrderNumber=@p_OrderNumber, @p_CustomerId=@p_CustomerId, @p_OrderDateBegin=@p_OrderDateBegin, @p_OrderDateEnd=@p_OrderDateEnd

end

首先我們用同樣的參數來執行一下查詢，當然沒問題，結果跟上面是一樣的。

所謂的參數化SQL，就是用變量當做占位符，通過 EXEC sp_executesql執行的時候將參數傳遞進去SQL中，在需要填入數值或數據的地方，使用參數 (Parameter) 來給值，

這樣的話，

第一，既能避免第一種寫法中的SQL注入問題（包括轉移符的處理），

因為參數是運行時傳遞進去SQL的，而不是編譯時傳遞進去的，傳遞的參數是什么就按照什么執行，參數本身不參與編譯

第二，保證執行計劃的重用，因為使用占位符來拼湊SQL的，SQL參數的值不同并導致最終執行的SQL文本不同

同上面，參數本身不參與編譯，如果查詢條件一樣（SQL語句就一樣），而參數不一樣，并不會影響要編譯的SQL文本信息

第三，還有就是避免了第二種情況（and (@p_CustomerId is null or CustomerId=@p_CustomerId)

或者 and OrderNumber=ISNULL( @p_OrderNumber,OrderNumber)）

這種寫法，查詢條件有就是有，沒有就是沒有，不會丟給SQL查詢引擎一個模棱兩個的結果，

避免了對索引的抑制行為，是一種比較好的處理查詢條件的方式。

缺點，對于這種方式，也有一點不好的地方，就是拼湊的字符串處理過程中，

調試具體的SQL語句的時候，參數是直接拼湊在SQL文本中的，不能直接執行，要手動將占位參數替換成具體的參數值

總結：

以上總結了三種在開發中比較常見的存儲過程的寫法，每種存儲過程的寫法可能在不同的公司都用應用，

是不是有人挑個最簡單最快捷（第二種）寫法，寫完不是完事了，而是埋雷了。

不是太熟悉SQL Server的同學可能會有點迷茫，有很多種寫法，究竟要用哪種寫法這些寫法之間有什么區別。

本文通過一個簡單的示例，說了常見的幾種寫法之間的區別，每種方式存在的問題，以及孰優孰劣，請小伙伴們明辨。

數據庫大神請無視，謝謝。