最近發現還有不少做開發的小伙伴，在寫存儲過程的時候，在參考已有的不同的寫法時，往往很迷茫，
不知道各種寫法孰優孰劣，該選用哪種寫法，以及各種寫法的優缺點，本文以一個簡單的查詢存儲過程為例，簡單說一下各種寫法的區別，以及該用那種寫法
專業DBA以及熟悉數據庫的同學請無視。
廢話不多說，上代碼說明，先造一個測試表待用，簡單說明一下這個表的情況
類似訂單表，訂單表有訂單ID，客戶ID，訂單創建時間等，查詢條件是常用的訂單ID，客戶ID，以及訂單創建時間
create table SaleOrder
(
id int identity(1,1),
OrderNumber int ,
CustomerId varchar(20) ,
OrderDate datetime ,
Remark varchar(200)
)
GO
declare @i int=0
while @i<100000
begin
insert into SaleOrder values (@i,CONCAT('C',cast(RAND()*1000 as int)),GETDATE()-RAND()*100,NEWID())
set @i=@i+1
end
create index idx_OrderNumber on SaleOrder(OrderNumber)
create index idx_CustomerId on SaleOrder(CustomerId)
create index idx_OrderDate on SaleOrder(OrderDate)
生成的測試數據大概就是這個樣子的
下面演示說明幾種常見的寫法以及每種寫法潛在的問題
更多linux內核視頻教程文本資料免費獲取后臺私信【內核】。
第一種常見的寫法:拼湊字符串，用EXEC的方式執行這個拼湊出來的字符串，不推薦
create proc pr_getOrederInfo_1
(
@p_OrderNumber int ,
@p_CustomerId varchar(20) ,
@p_OrderDateBegin datetime ,
@p_OrderDateEnd datetime
)
as
begin
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
)
as
begin
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，會抑制索引的情況
如圖，帶入參數值執行存儲過程，先忽略另外三個查詢字段，只傳入@p_CustomerId參數，
相關查詢列上（CustomerId）有索引，但是這里走的是CustomerId列上的Index Scan而非預期的Index Seek
糾錯：上面的一句話，使用參數做編譯的時候，是知道參數的值的（只有使用本地變量的時候才不知道具體的參數值，直接使用參數確實是知道的），
編譯也是根據具體的參數值來生成執行計劃的，但是為什么即使知道具體的參數值的情況下，依然生成一個Index Scan的方式，而不是期望的Index Seek?
即便是存儲過程在編譯的時候知道了參數的值，為什么仍舊用不到索引？
還要從and (@p_CustomerId is null or CustomerId = @p_CustomerId)這種寫法入手分析。
即便是CustomerId列上有索引，
如果@p_CustomerId 參數非空，走索引Seek完全沒有問題。
如果@p_CustomerId 為null，此時and (@p_CustomerId is null or CustomerId = @p_CustomerId)這個條件恒成立，如果再走索引Seek會出現什么結果？
語義上變成了是查找CustomerId 為null的值，如果采用Index Seek的方式執行，這樣的話邏輯上已經錯誤了。
因此出現這種寫法，為了安全起見，優化器只能選擇一個索引的掃描（即便是字段上有索引的情況下）
可以認為是這種寫法在語義支持不了相關索引的Seek，而索引的Scan是處理這種寫法的一種安全的方式
The optimiser can tell that and it plays safe. It creates plans that will always work.
That’s (one of the reasons) why in the first example it was an index scan, not an index seek.
參考這里，可以簡單地理解成這種寫法，語義上支持不了索引的Seek，最多支持到index scan
至于(@p_CustomerId is null or CustomerId = @p_CustomerId )這種寫法遇到本地變量的時候，
為什么抑制到到索引的使用，我之前也是沒有弄清楚的，評論中10樓Uest 給出了解釋，這里非常感謝Uest
如下
如果我直接帶入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，折騰了好久才發現這個嚴重的邏輯錯誤
對于這種寫法，
不管是第一點說的抑制索引的問題，數據量大的時候是非常嚴重的，上述寫法會造成全表（索引）掃描，有索引也用不上，至于全表（索引）掃描的壞處就不說了
還是第二點說的造成的邏輯錯誤，都是非常致命的
所以這種方式是最不值得推薦的。
第三種常見的寫法：參數化SQL，推薦
create proc pr_getOrederInfo_3
(
@p_OrderNumber int ,
@p_CustomerId varchar(20) ,
@p_OrderDateBegin datetime ,
@p_OrderDateEnd datetime
)
as
begin
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查詢引擎一個模棱兩個的結果，
避免了對索引的抑制行為，是一種比較好的處理查詢條件的方式。
缺點，1,對于這種方式，也有一點不好的地方，就是拼湊的字符串處理過程中，
調試具體的SQL語句的時候，參數是直接拼湊在SQL文本中的，不能直接執行，要手動將占位參數替換成具體的參數值
2,可能存在parameter sniff問題，但是對于parameter sniff問題，不是否定參數化SQL的重點，當然解決parameter sniff問題的辦法還是有的。
總結：
以上總結了三種在開發中比較常見的存儲過程的寫法，每種存儲過程的寫法可能在不同的公司都用應用，
是不是有人挑個最簡單最快捷（第二種）寫法，寫完不是完事了，而是埋雷了。
不是太熟悉SQL Server的同學可能會有點迷茫，有很多種寫法，究竟要用哪種寫法這些寫法之間有什么區別。
本文通過一個簡單的示例，說了常見的幾種寫法之間的區別，每種方式存在的問題，以及孰優孰劣，請小伙伴們明辨。
數據庫大神請無視，謝謝。
要用哪種寫法這些寫法之間有什么區別。
本文通過一個簡單的示例，說了常見的幾種寫法之間的區別，每種方式存在的問題，以及孰優孰劣，請小伙伴們明辨。
數據庫大神請無視，謝謝。
