Create PROCEDURE [dbo].[Proc_B2B_GetBatchMainPaging]

  @StationNo AS varchar() , --m

  @StationName AS varchar() , --m

  @PartNo AS varchar() ,   -- son

  @PartName AS varchar() , --son

  @IsOldPart AS varchar() , --m

  @IsRebate AS varchar() , --m

  @IsTransport AS varchar() , --m

  @startApplyTime AS varchar() ,   --m

  @endApplyTime AS  varchar() , --m

  @startExpectDate AS varchar(),  --m

  @endExpectDate AS varchar() , --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() NULL , -- son

[PartName] varchar() NULL , -- son

[Counts] DECIMAL(,) NULL ,   -- son

[ApplyPrice] [decimal](, ) NULL,  -- son

[ApprovalPrice] [decimal](, ) NULL,  -- son

[IsOldPart]  [varchar]() NULL, --m

[StationNo] [varchar]() NOT NULL, --m

[StationName][varchar]() NOT NULL, --m

[ExpectDate] [datetime] NULL, --m

[OrderStatus] [varchar]() NULL, --m

[ApplyTime] [datetime] NULL, --m

[CurrnetApprover] [varchar]() NULL, --D

[ApprovalTime] [datetime] NULL, --D

[Supply] [varchar]() NULL --s

);

CREATE TABLE #ReturnData (

[PageIndex] varchar() NULL ,

[PageSize] varchar() NULL ,

[totalNumber] varchar() NULL ,

[PageCount] varchar() 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 ='

   set @strSql= @strSql + ' AND convert(varchar(100), m.ApplyTime,21)>= ''' + convert(varchar(), @startApplyTime,)  + ''' '

   set @strSql= @strSql + ' AND convert(varchar(100), m.ApplyTime,21)<= ''' + convert(varchar(), @endApplyTime,) + ''' '

   set @strSql= @strSql + ' AND convert(varchar(100), m.ExpectDate,21)>= ''' +convert(varchar(), @startExpectDate,)  + ''' '

   set @strSql= @strSql + ' AND convert(varchar(100), m.ExpectDate,21)<= ''' +convert(varchar(), @endExpectDate,)  + ''' '

          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-)*@size)+) and (@page*@size)

      select * from  #ReturnData;

  --删除临时表

  drop table #ReturnTable