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

  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
上一篇:form表单里的故事


下一篇:Hadoop 2.7.3 HA 搭建及遇到的一些问题