SQLServer存储过程的⼏种常见写法分析，我们该⽤那种写法本⽂出处：
最近发现还有不少做开发的⼩伙伴，在写存储过程的时候，在参考已有的不同的写法时，往往很迷茫，
不知道各种写法孰优孰劣，该选⽤那种写法，以及各种写法优缺点，本⽂以⼀个简单的查询存储过程为例，简单说⼀下各种写法的区别，以及该⽤那种写法
专业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)
⽣成的测试数据⼤概就是这个样⼦的
下⾯演⽰说明⼏种常见的写法以及每种写法潜在的问题
第⼀种常见的写法：拼凑字符串，⽤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存储过程实例  因为⼀条低效⽽⼜频繁执⾏的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，折腾了好久才发现这个严重的逻辑错误
    对于这种写法，
    不管是第⼀点说的抑制索引的问题，数据量⼤的时候是⾮常严重的，上述写法会造成全表扫描，有索引页⽤不上，⾄于全表扫描的坏处就不说了
    还是第⼆点说的造成的逻辑错误，都是⾮常致命的
    所以这种⽅式是最最不推荐的。
第三种常见的写法：参数化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')

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。