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
转载于:https://www.cnblogs.com/szlblog/p/8319830.html