sqlserver存储过程中处理多个查询条件的几种常见写法分析，我们该用那种写法(代码片段)

create table SaleOrder
(
    id 　　　　　　int identity(1,1),
    OrderNumber  int　　　　　　　　 ,
    CustomerId   varchar(20)      ,
    OrderDate    datetime         ,
    Remark       varchar(200)
)GOdeclare @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)

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

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

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\'

SELECT [id]
    　　　　　　　　　　,[OrderNumber]
    　　　　　　　　　　,[CustomerId]
    　　　　　　　　　　,[OrderDate]
    　　　　　　　　　　,[Remark]
　　　　　　　　　　FROM [dbo].[SaleOrder] 
　　　　　　　　　　where 1=1  and CustomerId  = \'C\'88\'

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

/*
这是另外一种类似的奇葩的写法,需要重点关注，真的就能满足“不管参数是否为空都满足”
        　　　　and  OrderNumber = ISNULL( @p_OrderNumber,OrderNumber)
        　　　　and  CustomerId  = ISNULL( @p_CustomerId,CustomerId)
*/

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