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

 
  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