Sql 语句拼接 多条件分页查询

  Create PROCEDURE [dbo].[Proc_B2B_GetBatchMainPaging]
  @StationNo AS varchar(50) , --m
  @StationName AS varchar(50) , --m
  @PartNo AS varchar(50) ,   -- son
  @PartName AS varchar(200) , --son
  @IsOldPart AS varchar(1) , --m
  @IsRebate AS varchar(1) , --m
  @IsTransport AS varchar(1) , --m
  @startApplyTime AS varchar(200) ,   --m
  @endApplyTime AS  varchar(200) , --m
  @startExpectDate AS varchar(200),  --m
  @endExpectDate AS varchar(200) , --m
  @page AS int , --
  @size AS int  --
 -- B2B_BatchPartMain as m
 -- B2B_BatchApprovalDetail as D
 -- B2B_BatchSendPartInfo as s
 -- B2B_BatchPartsInfo as son
AS
BEGIN


CREATE TABLE #ReturnTable ( 
[PartNo] varchar(20) NULL , -- son
[PartName] varchar(200) NULL , -- son
[Counts] DECIMAL(18,2) NULL ,   -- son
[ApplyPrice] [decimal](18, 2) NULL,  -- son
[ApprovalPrice] [decimal](18, 2) NULL,  -- son
[IsOldPart]  [varchar](1) NULL, --m
[StationNo] [varchar](8) NOT NULL, --m
[StationName][varchar](100) NOT NULL, --m
[ExpectDate] [datetime] NULL, --m
[OrderStatus] [varchar](4) NULL, --m
[ApplyTime] [datetime] NULL, --m
[CurrnetApprover] [varchar](20) NULL, --D
[ApprovalTime] [datetime] NULL, --D
[Supply] [varchar](200) NULL --s
);


CREATE TABLE #ReturnData ( 
[PageIndex] varchar(20) NULL , 
[PageSize] varchar(200) NULL , 
[totalNumber] varchar(20) NULL , 
[PageCount] varchar(50) NULL , 
);


 DECLARE @strSql varchar(MAX);  
  --拼接Sql语句
set @strSql='
select 
son.PartNo,
son.PartName,
son.Counts,
son.ApplyPrice,
son.ApprovalPrice,
m.IsOldPart,
m.StationNo,
m.StationName,
m.ExpectDate,
m.OrderStatus,
ApplyTime,
D.CurrnetApprover,
d.ApprovalTime,
s.Supply

from  B2B_BatchPartMain as m 
JOIN B2B_BatchPartsInfo as son ON m.BOrderNo=son.BOrderNo 
JOIN B2B_BatchApprovalDetail as D  ON  m.BOrderNo=D.BOrderNo 
JOIN B2B_BatchSendPartInfo AS S ON  m.BOrderNo=S.BOrderNo 
where 1=1'
   set @strSql= @strSql + ' AND convert(varchar(100), m.ApplyTime,21)>= ''' + convert(varchar(100), @startApplyTime,21)  + ''' ' 
   set @strSql= @strSql + ' AND convert(varchar(100), m.ApplyTime,21)<= ''' + convert(varchar(100), @endApplyTime,21) + ''' '
   set @strSql= @strSql + ' AND convert(varchar(100), m.ExpectDate,21)>= ''' +convert(varchar(100), @startExpectDate,21)  + ''' ' 
   set @strSql= @strSql + ' AND convert(varchar(100), m.ExpectDate,21)<= ''' +convert(varchar(100), @endExpectDate,21)  + ''' '

          if( @StationNo is not null) 
             begin    
                 set @strSql= @strSql + ' and m.StationNo =''' + @StationNo+'''' ;
             end
           --判断拼接Sql语句
           if( @StationName is not null) and (@StationName!='')
             begin    
                set  @strSql= @strSql + '  and  m.StationName =''' + @StationName+'''' ;
             end
           if( @PartNo is not null)and (@PartNo!='')
             begin 
                set  @strSql= @strSql + ' and son.PartNo =''' + @PartNo+'''' ;
             end
           if( @PartName is not null)and (@PartName!='')
             begin 
                set  @strSql= @strSql + ' and  son.PartName =''' + @PartName+'''' ;
             end
           if( @IsOldPart is not null)and (@IsOldPart!='')
             begin 
                set  @strSql= @strSql + ' and  m.IsOldPart =''' + @IsOldPart+'''' ;
             end
           if( @IsRebate is not null)and (@IsRebate!='')
             begin 
                set  @strSql= @strSql + ' and  m.IsRebate =''' + @IsRebate+'''' ;
             end
          if( @IsTransport is not null)and (@IsTransport!='')
             begin 
                set  @strSql= @strSql + ' and   m.IsTransport =''' + @IsTransport+'''' ;
             end
           --插入数据到临时表
      insert INTO #ReturnTable  exec(@strSql) ;
      --总数量
      DECLARE   @totalNumber  as INT  ;
         select @totalNumber=COUNT(PartNo) from #ReturnTable;
      -- 总页数
      DECLARE @PageCount  as INT  ;
         SET @PageCount= Ceiling(@totalNumber*1.0/@size);
      --插入返回信息
      INSERT into #ReturnData VALUES(@page,@size,@totalNumber,@PageCount)
      --分页查询数据
      select * from (select ROW_NUMBER() over(order by PartNo asc) as rowNumber  , * from #ReturnTable) as temp  where rowNumber between (((@page-1)*@size)+1) and (@page*@size)
      select * from  #ReturnData;

    
  --删除临时表
  drop table #ReturnTable