CREATE PROCEDURE PAGE
@tblName VARCHAR(255), -- 表名
@strGetFields VARCHAR(1000)='*', -- 需要返回的列
@fldName VARCHAR(255)='', -- 排序的字段名
@PageSize INT=10, -- 页尺寸
@PageIndex INT=1, -- 页码
@doCount BIT=0, -- 返回记录总数, 非 0 值则返回
@OrderType BIT=0, -- 设置排序类型, 非 0 值则降序
@strWhere VARCHAR(1500)='' -- 查询条件 (注意: 不要加 where)
AS
DECLARE @strSQL VARCHAR(5000) -- 主语句
DECLARE @strTmp VARCHAR(110) -- 临时变量
DECLARE @strOrder VARCHAR(400) -- 排序类型
IF @doCount!=0
BEGIN
IF @strWhere!=''
SET @strSQL = 'select count(*) as Total from ['+@tblName+'] where '+
@strWhere
ELSE
SET @strSQL = 'select count(*) as Total from ['+@tblName+']'
END--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况
ELSE
BEGIN
IF @OrderType!=0
BEGIN
SET @strTmp = '<(select min'
SET @strOrder = ' order by ['+@fldName+'] desc'
--如果@OrderType不是0,就执行降序,这句很重要！
END
ELSE
BEGIN
SET @strTmp = '>(select max'
SET @strOrder = ' order by ['+@fldName+'] asc'
END
IF @PageIndex=1
BEGIN
IF @strWhere!=''
SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+
' from ['+@tblName+'] where '+@strWhere+' '+@strOrder
ELSE
SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+
' from ['+@tblName+'] '+@strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
END
ELSE
BEGIN
--以下代码赋予了@strSQL以真正执行的SQL代码
SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+
' from ['
+@tblName+'] where ['+@fldName+']'+@strTmp+'(['+@fldName+
']) from (select top '+STR((@PageIndex-1)*@PageSize)+' ['+@fldName
+'] from ['+@tblName+']'+@strOrder+') as tblTmp)'+@strOrder
IF @strWhere!=''
SET @strSQL = 'select top '+STR(@PageSize)+' '+@strGetFields+
' from ['
+@tblName+'] where ['+@fldName+']'+@strTmp+'(['
+@fldName+']) from (select top '+STR((@PageIndex-1)*@PageSize)
+' ['
+@fldName+'] from ['+@tblName+'] where '+@strWhere+' '
+@strOrder+') as tblTmp) and '+@strWhere+' '+@strOrder
END
END
EXEC (@strSQL)
GO
--调用：
-- PAGE '表','*','id',123456,9,0
--游标查询
CREATE procedure cursorPage
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
GO
--调用
exec cursorPage 'select * from dbo.费用表 where 销帐日期=''2010-05-16 00:00:00.000''',1,10
CREATE PROC page @pagelinenum INT,--一页显示多少行
@pagenum INT --第几页
AS
SELECT *
FROM 费用表
WHERE 费用ID BETWEEN @pagelinenum * ( @pagenum - 1 ) + 1 AND @pagelinenum * @pagenum
GO
--调用
page 30, 56
--2012
DECLARE @PageSize TINYINT = 5,
@CurrentPage INT = 1;
SELECT BusinessId,CityID,ServiceShopName
FROM Business_Login
where IsShow=0
ORDER BY OrderId
OFFSET (@PageSize * (@CurrentPage - 1))
ROWS
FETCH NEXT @PageSize ROWS ONLY;
--05
DECLARE @pagesize AS INT, @pagenum AS INT;
SET @pagesize = 5;
SET @pagenum = 1;
WITH SalesRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderId) AS rownum,
BusinessId,CityID,ServiceShopName
FROM Business_Login
where IsShow=0
)
SELECT rownum,BusinessId,CityID,ServiceShopName
FROM SalesRN
WHERE rownum > @pagesize * (@pagenum-1)
AND rownum <= @pagesize * @pagenum
ORDER BY rownum;
