分页存储过程--From：桌面备份 -> sql2005新功能.docx

二、以下示例将返回行号为 50 到 60（含）的行，并以 OrderDate 排序。

USE AdventureWorks;

GO

WITH OrderedOrders AS

(SELECT SalesOrderID, OrderDate,

ROW_NUMBER() OVER (order by OrderDate)as RowNumber

FROM Sales.SalesOrderHeader )

SELECT *

FROM OrderedOrders

WHERE RowNumber between 50 and 60;

三、实现分页存储过程(固定到某个表的数据)

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:      cxw

-- Create date: 2006/11/14

-- Description: 利用Row_Number函数实现分页存储过程

-- exec DividePageForROW_NUMBER 2,1,‘‘,‘‘

-- =============================================

Create PROCEDURE DividePageForROW_NUMBER

@iPageSize      int         --每页数

,@iIndexPage    int         --第几页

,@vOrderBy      varchar(50) --排序字段

,@iRowCount     int         --总记录数

AS

BEGIN

SET NOCOUNT ON;

with PageTable as

(

select row_number() over(order by ReasonID) TID,* from A

)

select *

from PageTable

where TID between ((@iIndexPage-1)*@iPageSize+1) and @iPageSize*@iIndexPage

SET NOCOUNT OFF;

END

GO

use study;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:  cxw
-- Create date: 2006/11/14
-- Description: 利用Row_Number函数实现分页存储过程(输出总记录数用返回一个字段)
--select * from OrderDetail
/*
--table表的分页
exec [PublicPageQuery] @vQueryString=‘OrderDetail‘
,@vKeyID=‘OrderNo‘
,@vQueryType=‘table‘
--为空就取表里所有字段,否则取自已设定字段， 最好不要取所有，指定
,@vColunmString=‘OrderNo,ProdNo,ProdName‘ 
,@vIsHaveRowCount=‘no‘
,@iPageSize=5
,@iIndexPage=2
--查询字符串的分页(单一的查询条件)
exec [PublicPageQuery] @vQueryString=‘select OrderNo,ProdNo,ProdName 
from  OrderDetail 
where OrderNo like ‘‘%So%‘‘
and ProdNo like ‘‘%%‘‘‘
,@vKeyID=‘OrderNo‘
,@vQueryType=‘select‘
,@vColunmString=‘OrderNo,ProdNo,ProdName‘ 
,@vIsHaveRowCount=‘no‘
,@iPageSize=5
,@iIndexPage=2
--带有关联的查询字符串
exec [PublicPageQuery] @vQueryString=‘SELECT * 
 FROM(
  SELECT * FROM (
  SELECT RequestNo,RequestTypeID,RequestType,RequestUser,NeedToAddress,NeedArriveTime,Memo,Status
  FROM ERPDATA.dbo.TP_RequestOfficeCar
  UNION
  SELECT A.RequestNo,A.RequestTypeID,RequestType,RequestUser,B.CustomerAddress as NeedToAddress,B.NeedArriveTime,A.Memo,A.Status
  FROM ERPDATA.dbo.TP_RequestDeliverGoodsCar A,ERPDATA.dbo.TP_RequestDeliverGoodsCarDetail B
  WHERE A.RequestNo=B.RequestNo
  ) M
  WHERE  isnull(RequestUser,‘‘) LIKE ‘‘%%‘‘
   AND isnull(NeedToAddress,‘‘) LIKE ‘‘%%‘‘
   AND isnull(Status,‘‘) like ‘‘%%‘‘
   AND isnull(Memo,‘‘) LIKE ‘‘%%‘‘ ) Main  ‘
,@vKeyID=‘OM.OrderNo‘
,@vQueryType=‘select‘
,@vColunmString=‘OrderNo,BillTo,BillToName‘
,@vIsHaveRowCount=‘no‘
,@iPageSize=5
,@iIndexPage=1
*/
-- =============================================
Create PROCEDURE [dbo].[PublicPageQuery]

@vQueryString  nvarchar(MAX)  --查询字符串,或表名
,@vKeyID   varchar(200)  --关键字(排序字段,索引字段,最好用索引字段,大大提高分页查询速度)
,@vQueryType  varchar(20)   --查询类型，为表(table)，查询语句(select)
,@vColunmString  nvarchar(2000)  --所显示字段值(最好都添上此字段名)
,@vIsHaveRowCount varchar(5)   --是否显示总记录数,有(yes),不(no)
,@iPageSize  int      --每页数
,@iIndexPage int      --第几页

AS
BEGIN
SET NOCOUNT ON;
declare @vSQLString varchar(8000) --查询SQL字符串
,@vRowCountString varchar(50)  --自动增量统计查询字符串
,@vRowNumberString varchar(200)  --自动增量查询字符串
,@vWhereKeyStr varchar(10)   --条件关键字
,@iStart int      --开始位置
,@iEnd int       --结束位置
--将SQL语句优化,去掉没有必要的查询条件
--select * from  dbo.RecurrentSplit(@vQueryString,‘and ‘,1,1)
--where isnull(SplitName,‘‘)<>‘‘
--return
set @vWhereKeyStr=‘%%‘  --所要去掉的关键查询条件
if(charindex(@vWhereKeyStr,@vQueryString)=0)
begin
 goto lbl_Query
end
set @vQueryString=replace(@vQueryString,‘
‘,‘ ‘) --去掉回车符
select @vQueryString=case when  A.IndexNo>0 --最少保留第一条查询条件，第一个条件最好最经常查询字段条件
 then 
   case when charindex(@vWhereKeyStr,B.SplitName)>0 
   then 
    --只替换所需要修改的无条件的字符串。
    replace(@vQueryString
     ,substring(B.SplitName,0,charindex(@vWhereKeyStr,B.SplitName)+len(@vWhereKeyStr)+2),‘‘) 
   else @vQueryString 
  end
 else
  @vQueryString
 end
--select @vQueryString=
--  case when charindex(‘%%‘,B.SplitName)>0 
--  then replace(@vQueryString,B.SplitName,‘‘) 
--  else @vQueryString 
--  end
from 
(
select * from  dbo.RecurrentSplit(@vQueryString,‘and ‘,1,1)--字符串分割函数，之前有
where isnull(SplitName,‘‘)<>‘‘
) A
cross apply dbo.RecurrentSplit(A.SplitName,‘or ‘,1,1) B
--set @vQueryString=case when charindex(‘‘‘‘,@vQueryString,charindex(‘where‘,@vQueryString))>0
--then @vQueryString else left(@vQueryString,charindex(‘where‘,@vQueryString)-1) end
--select @vQueryString
--return
--查询断点
lbl_Query:
select @vSQLString=‘‘
,@vRowNumberString=‘PageIndex=row_number() over(order by ‘+@vKeyID+‘)‘
,@vQueryString=ltrim(rtrim(@vQueryString))
,@vColunmString=case when @vQueryType=‘table‘
 then
  case when @vColunmString=‘‘ 
  then ‘*‘
  else @vColunmString 
  end
 else --取出所要取出的字段
  case when @vColunmString=‘‘
  then
   substring(@vQueryString,len(‘select‘)+1,charindex(‘from‘,@vQueryString)-len(‘select‘)-1)
  else @vColunmString end
 end
,@vQueryString=case  @vQueryType when ‘table‘ 
 then ‘select ‘+@vRowNumberString+char(13)
  +‘,‘+@vColunmString+‘ from ‘+@vQueryString 
 when ‘select‘ 
 then ‘select ‘+@vRowNumberString+char(13)
 +‘,‘+right(@vQueryString,len(@vQueryString)-len(‘select‘))--‘(‘+@vQueryString+‘)Alias‘ 
 else ‘procedure‘
 end
,@vRowCountString=case when @vIsHaveRowCount=‘yes‘ 
 then ‘,(select count(0) from PageTable) ‘‘RowCount‘‘‘
 else ‘‘ end
,@iStart=(@iIndexPage-1)*@iPageSize+1
,@iEnd=@iPageSize*@iIndexPage

set @vSQLString=‘with PageTable as
(
‘+@vQueryString+‘
)
select ‘
+@vColunmString+char(13)
+case when @vRowCountString=‘‘ then ‘‘ else @vRowCountString+char(13) end
+‘from PageTable
where PageIndex between ‘+cast(@iStart as varchar)+‘ and ‘+cast(@iEnd as varchar)
print @vSQLString
exec(@vSQLString)
SET NOCOUNT OFF;

END