sqlserver存储过程的几种常见写法分析,我们该用那种写法

专注,勤学,慎思。戒骄戒躁,谦虚谨慎 专注,勤学,慎思。戒骄戒躁,谦虚谨慎     2022-08-08     617

关键词:

 

本文出处: 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)
)
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,拖垮一台服务器也是司空见惯
  但是呢,问题非常多,也非常非常不推荐,甚至比第一种方式更糟糕。

  分析一下这种处理方式的逻辑:
  这种处理方式,因为不确定查询的时候到底有没有传入参数,也就数说不能确定某一个查询条件是否生效,
  于是就采用类似 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

      

 

      为什么说可能会抑制到索引的时候?上面提到过,SQL在执行之前是需要编译的,
      因为在编译的时候并不知道查询条件是否传入了值,有可能为null,有可能是一个具体的值

       纠错:上面的一句话,使用参数做编译的时候,是知道参数的值的(只有使用本地变量的时候才不知道具体的参数值,直接使用参数确实是知道的),
          编译也是根据具体的参数值来生成执行计划的,但是为什么即使知道具体的参数值的情况下,依然生成一个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,折腾了好久才发现这个严重的逻辑错误 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
)
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问题的办法还是有的,

       参考:http://www.cnblogs.com/wy123/p/5645485.html

 

总结:

  以上总结了三种在开发中比较常见的存储过程的写法,每种存储过程的写法可能在不同的公司都用应用,
  是不是有人挑个最简单最快捷(第二种)写法,写完不是完事了,而是埋雷了。
  不是太熟悉SQL Server的同学可能会有点迷茫,有很多种写法,究竟要用哪种写法这些写法之间有什么区别。
  本文通过一个简单的示例,说了常见的几种写法之间的区别,每种方式存在的问题,以及孰优孰劣,请小伙伴们明辨。
  数据库大神请无视,谢谢。

 

学习安排

10.31-11.061..Net下几个服务框架介绍http://www.cnblogs.com/beyondbit/p/6019954.html2.ASP.NETMVC使用FluentScheduler定时器计划任务http://www.cnblogs.com/mafly/p/FluentScheduler.html3.SQLServer存储过程的几种常见写法分析,我们该用那种写法http:// 查看详情

js面向对象的几种常见写法

下面是一个简单的js对象:定义Circle类,拥有成员变量r,常量PI和计算面积的成员函数area(),常用为第一种和第三种。1.工厂方式varCircle=function(){varobj=newObject();obj.PI=3.14159;obj.area=function(r){returnthis.PI*r*r;}returnobj;}varc=newCircle();alert(c. 查看详情

常见的几种单例模式写法(代码片段)

  单例模式:是一种常用的软件设计模式,在它的核心结构中值包含一个被称为单例的特殊类。一个类只有一个实例,即一个类只有一个对象实例。  对于系统中的某些类来说,只有一个实例很重要,例如,一个系统中可以... 查看详情

分析电脑死机的常见的几种原因,以及对应的解决方法

很多的电脑用户都遇到过各种各样的电脑故障,例如花屏、黑屏、又或者是电脑无法开机、死机等等都是比较常见的故障,为了修好电脑,我们今天就来了解下,当电脑经常死机,我们用什么方法解决呢?下面就跟随小编一起来... 查看详情

java单例模式常见的几种写法(代码片段)

单例模式有多种写法,都有各自的优缺点,最常见的优缺点就是【懒汉和饿汉】的比较,以及是否线程安全懒汉模式,节约内存,只有使用到时才创建单例对象,可能会有线程安全问题饿汉模式,浪费... 查看详情

sqlserver常见的几种分页方式(代码片段)

⒈offsetfetchnext方式【SqlServer2012及以上版本支持】【推荐】1select*fromT_User2orderbyid3offset5rows/*(页数-1)*条数*/4fetchnext5rowsonly/*条数*/⒉row_number()over()方式1select*from2(select*,row_number()over(orderbyid)asorderI 查看详情

数据分析师常见的几种思路

 1.简单趋势  通过实时访问趋势了解产品使用情况,便于产品迅速迭代。访问用户量、访问来源、访问用户行为三大指标对于趋势分析具有重要意义。2.多维分解  数据分析师可以根据分析需要,从多维度对指标进行分... 查看详情

谈谈垂直分割线的几种写法

谈谈分垂直分割线的几种写法,我们对这种分隔线不会陌生,在项目需求中会经常遇到,我们来看看下边的几种写法,方法一:<div><ahref="">注册</a>|<ahref="">登录</a></div>直接在文字中间插入短线字符;... 查看详情

分析总结常见的几种移动机器人底盘类型及其运动学

添加链接描述 查看详情

性能测试的几种常见方法

 性能测试的几种常见方法(转)  负载测试:负载测试是用户观点的测试行为。简单说来就是负载测试就是让系统在一定得负载压力下进行正常的工作,观察系统的表现能否满足用户的需求。  用户的需求从何而来?需... 查看详情

三栏布局的几种方法(代码片段)

...定的情况下,左右各留出300px,中间自适应,可以有以下的几种写法。比较常见的有浮动布局和绝对定位布局,但是flex布局现在也非常普遍了,Grid布局可能兼容性不太好,但也不失为一种方法。因为下面的css代码中有许多的相... 查看详情

awr收集缓慢挂起的几种常见情况分析

...断大打折扣。 以下列举AWR收集缓慢、挂起或缺失常见的几种情况:STATISTICS_LEVEL参数不为ALL或TYPICALSYSAUX表空间不足系统资源I/O、CPU使 查看详情

awr收集缓慢挂起的几种常见情况分析

...断大打折扣。 以下列举AWR收集缓慢、挂起或缺失常见的几种情况:STATISTICS_LEVEL参数不为ALL或TYPICALSYSAUX表空间不足系统资源I/O、CPU使 查看详情

awr收集缓慢挂起的几种常见情况分析

...断大打折扣。 以下列举AWR收集缓慢、挂起或缺失常见的几种情况:STATISTICS_LEVEL参数不为ALL或TYPICALSYSAUX表空间不足系统资源I/O、CPU使 查看详情

测试用例的几种常见设计方法

测试用例常见的设计方法有:等价类划分法、边界值分析法、错误推测法、判定表法、正交实验法。一.等价类划分法顾名思义,等价类划分,就是将测试的范围划分成几个互不相交的子集,他们的并集是全集,从每个子集选出... 查看详情

抓包过程中遇到的几种异常报文

概述:    在平时的运维过程中,我们经常会遇到一些数据传输的问题,在我们平时遇到的数据传输问题中定位难度从难到易基本为:数据传输慢不符合预期、数据传输过程有丢包、数据传输被终止或网络断开连接... 查看详情

loadrunner的几种常见错误

...误及解决办法1录制loadrunner脚本时,无法生成脚本,录制过程中事物都 查看详情

单例模式(下)---聊一聊单例模式的几种写法(代码片段)

在上一篇文章单例模式(上)—-如何优雅地保证线程安全问题中,我们采取了懒汉式写法来写我们的单例模式,并且重点讲解了懒汉式中线程安全的问题。这篇我们来讲讲单例模式中的其他几种写法。上篇文章中,方法和变量的声... 查看详情