不适用临时表进行分页，筛选，查询，避免高并发的方法。



ALTER proc [dbo].[GetD_OrderInfoCollectionForPaging]
(
   @PageIndex int,     ----当前的页码
  @PageSize int,  ----每页显示多少条   
  @RecordCount int output,----总条数
  @PageCount int output , ----总页数
  
  @TransactionType nvarchar(4000),-------交易类型编号
  @BusinessStatus nvarchar(4000),----交易状态
  @StrSeach nvarchar(4000),------搜索条件
     ----------排序标示：   0 无排序，1 升序 ，2 降序
  @OrderNum char(1),--------按照数量排序的标示
  @OrderMoney char(1),------按照金额排序的标示
  @OrderCreate char(1),-----按照交易时间排序的标示
  @OrderDiff char(1)----按照剩余时间排序的标示
)
as
begin
    declare @JPWhere  varchar(4000)----条件字符串
    declare @JPBy   varchar(4000) ----排序字符串(内层的)
    declare @JPByOut  varchar(4000)----排序字符串(外层的)
    declare @val nvarchar(4000)----定义一个参数用来接收T-SQL语句
    declare @sql nvarchar(4000)----定义一个参数用来接收T-SQL语句
     -----初次或者没有条件筛选，加载所有信息
 IF(@TransactionType='0' and @BusinessStatus='0'  and (@StrSeach=''or  @StrSeach is null ) and @OrderNum='0' and @OrderMoney='0' and @OrderCreate='0' and @OrderDiff='0')
  begin
  set @JPWhere='where 1=1 '
  set @JPBy=' order by o.CreateTime desc '
  set @JPByOut=' order by t.CreateTime desc '
  end
    -----有条件进行筛选
    else
 begin
 set @JPWhere='Where 1=1 '
 set @JPBy='order by o.CreateTime desc  '
 set @JPByOut=' order by t.CreateTime desc '
   if(@TransactionType!='0')--按照类别查询
   begin
   set @JPWhere= @JPWhere+ ' and o.TransactionType ='+@TransactionType
   end
   if(@BusinessStatus!='0')--按照状态查询
   begin
   set @JPWhere=@JPWhere+' and o.BusinessStatus='+@BusinessStatus
   end
   if(@StrSeach!='')---模糊查询
   begin
   set @JPWhere=@JPWhere+' and o.OrderCode like '''+ @StrSeach+'%'''  +' or p.ProjectName like'''+@StrSeach+'%'''
   end 
   if(@OrderNum='1')---按照数量排序
   begin
    set @JPBy=' order by  im.num asc '
    set @JPByOut=' order by t.num asc '
   end
   if(@OrderNum='2')
   begin
    set @JPBy=' order by  im.num desc '
      set @JPByOut=' order by t.num desc '
   end
   if(@OrderMoney='1')---按照金额排序
   begin
     set @JPBy=' order by  o.BusinessAmount asc '
     set @JPByOut=' order by  t.BusinessAmount asc '
   end
   if(@OrderMoney='2')
   begin
     set @JPBy=' order by  o.BusinessAmount desc '
     set @JPByOut=' order by  t.BusinessAmount desc '
   end
   if(@OrderCreate='1')---按照时间排序
   begin
     set @JPBy=' order by o.CreateTime asc '
     set @JPByOut=' order by t.CreateTime asc '
   end
    if(@OrderCreate='2')
   begin
     set @JPBy=' order by o.CreateTime desc '
     set @JPByOut=' order by t.CreateTime desc '
   end
   if(@OrderDiff='1')---按照剩余时间排序
   begin
   set @JPBy=' order by  DATEDIFF(HH,o.CreateTime,o.FinishDate) asc'
   set @JPByOut=' order by t.Diff asc '
   end
    if(@OrderDiff='2')
   begin
   set @JPBy=' order by  DATEDIFF(HH,o.CreateTime,o.FinishDate) desc'
   set @JPByOut=' order by t.Diff desc '
   end
 end
 set @val='select @count=count(1) from( select ROW_NUMBER() over(' +@JPBy+ ') rowNumber, o.OrderCode,p.ProjectName,o.TransactionType,im.num,c.CustomerName sellerName,e.CustomerName buyName,o.BusinessAmount,'
 +' o.CreateTime,DATEDIFF(HH,o.CreateTime,o.FinishDate) Diff,o.BusinessStatus'
 +' from D_OrderInfo o'
 +' left join D_ProjectInfo p on p.ProjectID=o.ProjectID'
 +' left join (select i.ProjectID,COUNT(*) num from D_ProjectImageInfo i where i.IsDeleted=0 or i.IsDeleted is null group by i.ProjectID) im on im.ProjectID=o.ProjectID'
 +' left join E_CustomerInfo c on c.CustomerID=o.Seller'
 +' left join E_CustomerInfo e on e.CustomerID=o.CreatorID '+@JPWhere+' ) t'
 -----执行这条拼接的字符串
 --exec(@val)
 --print (@val)
 ----执行过以后，会创建出来一张全局表##temp（里面包含所有的条件过滤后的数据）
 
 ----查询出来总共的记录数
 --select  @RecordCount = COUNT(1) from ##tablejp
 EXEC sp_executesql @val, N'@count int output',@RecordCount OUTPUT
 ----查询出来总共的页数
 SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
 
 ----对查询的数据集进行分页
 set @sql='select  OrderCode,TransactionType,BusinessAmount,
 CreateTime,BusinessStatus,Convert(nvarchar,isnull(num,0))+'';''+isnull(sellerName,'''')+'';''+isnull(buyName,'''')+'';''+Convert(nvarchar,isnull(Diff,0))+'';''+isnull(ProjectName,'''') Remark
 from (select ROW_NUMBER() over(' +@JPBy+ ') rowNumber, o.OrderCode,p.ProjectName,o.TransactionType,im.num,c.CustomerName sellerName,e.CustomerName buyName,o.BusinessAmount,
  o.CreateTime,DATEDIFF(HH,o.CreateTime,o.FinishDate) Diff,o.BusinessStatus
  from D_OrderInfo o
  left join D_ProjectInfo p on p.ProjectID=o.ProjectID
  left join (select i.ProjectID,COUNT(*) num from D_ProjectImageInfo i where i.IsDeleted=0 or i.IsDeleted is null group by i.ProjectID) im on im.ProjectID=o.ProjectID
  left join E_CustomerInfo c on c.CustomerID=o.Seller
  left join E_CustomerInfo e on e.CustomerID=o.CreatorID '+@JPWhere+') t where rowNumber between ' + STR(@PageIndex * @PageSize + 1) + ' and ('
            + STR(@PageIndex + 1) + ') * ' + STR(@PageSize) + ' '+@JPByOut
            print @sql
 EXEC sp_executesql @sql
end