本文出处：

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的同学可能会有点迷茫，有很多种写法，究竟要用哪种写法这些写法之间有什么区别。

本文通过一个简单的示例，说了常见的几种写法之间的区别，每种方式存在的问题，以及孰优孰劣，请小伙伴们明辨。

数据库大神请无视，谢谢。