AS IF @FdShow IS NULL OR LTRIM(RTRIM(@FdShow)) = '' BEGIN SET @FdShow = '*' END
SET @Where = ISNULL(@Where,'') SET @Where = LTRIM(RTRIM(@Where)) IF @Where <> '' BEGIN IF UPPER(SUBSTRING(@Where,1,5)) <> 'WHERE' BEGIN SET @Where = 'WHERE 1 = 1 ' + @Where END END
IF @PageSize > 0 BEGIN SET @FdOrder = NULLIF(LTRIM(RTRIM(@FdOrder)),'') SET @FdOrder = ISNULL(@FdOrder,'id') END
SET @FdOrder = LTRIM(RTRIM(@FdOrder)) IF @FdOrder <> '' BEGIN IF UPPER(SUBSTRING(@FdOrder,1,8)) <> 'ORDER BY' BEGIN SET @FdOrder = 'ORDER BY ' + @FdOrder END END
-- 获取记录总数 set @SqlCount = 'select @RecordSum = count(*) from ' + @Table + ' ' + @Where exec sp_executesql @SqlCount, N'@RecordSum int output', @RecordSum output
IF @RecordSum = 0 SET @Page = 0 ELSE IF (@RecordSum - 1) / @PageSize + 1 < @Page SET @Page = (@RecordSum - 1) / @PageSize + 1
IF @PageSize = -1 BEGIN SET @SqlQuery = ' SELECT ' + @FdShow + ' FROM '+@Table+' '+ @Where END ELSE BEGIN SET @SqlQuery = ' SELECT ' + @FdShow + ',RowNumber FROM ( SELECT ' + @FdShow + ',ROW_NUMBER() OVER( '+ @FdOrder +') AS RowNumber FROM '+@Table+' '+ @Where +') AS RowNumberTableSource WHERE RowNumber BETWEEN ' + CAST(((@Page - 1) * @PageSize+1) AS VARCHAR) + ' AND ' + CAST((@Page * @PageSize) AS VARCHAR) END
EXECUTE(@SqlQuery)
RETURN 0 GO
GRANT EXEC ON sp_Pages TO PUBLIC
GO
DECLARE @pagesize AS INT, @pagenum AS INT; SET @pagesize = 5; SET @pagenum = 2;WITH SalesCTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY qty, empid) AS rownum, empid, mgrid, qty FROM dbo.Sales ) SELECT rownum, empid, mgrid, qty FROM SalesCTE WHERE rownum > @pagesize * (@pagenum-1) AND rownum <= @pagesize * @pagenum ORDER BY rownum;
SQL 2005的ROW_NUMBER()实现分页功能DECLARE @pagenum AS INT, @pagesize AS INT SET @pagenum = 2 SET @pagesize = 3 SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum, newsid, topic, ntime, hits FROM news) AS D WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize ORDER BY newsid DESC aspx里面只需给SQL传入pageid和条数即可。 CSDN上还有个存储过程实现分页的代码: ALTER PROCEDURE news_Showlist ( @tblName varchar(255), -- 表名 @strGetFields varchar(1000), -- 需要返回的列 @fldName varchar(255), -- 排序的字段名 @PageSize int , -- 页尺寸 @PageIndex int , -- 页码 @strWhere varchar(1500), -- 查询条件(注意: 不要加where) @Sort varchar(255) --排序的方法) AS declare @strSQL varchar(5000) -- 主语句 declare @strTmp varchar(110) -- 临时变量 declare @strOrder varchar(400) -- 排序类型if @Sort = 'desc' begin set @strTmp = '<(select min' set @strOrder = ' order by ' + @fldName +' desc' --如果@OrderType不是,就执行降序,这句很重要! end else begin set @strTmp = '>(select max' set @strOrder = ' order by ' + @fldName +' asc' end
if @PageIndex = 1 begin if @strWhere != '' begin set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder end else begin set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder end --如果是第一页就执行以上代码,这样会加快执行速度 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 exec (@strSQL) RETURN
----------------------------------------------------------- Author : SQL2005分页存储过程htl258(Tony)-- Date : 2009-09-22 13:00:50-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)-----------------------------------------------------------创建测试表IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb]([Col] NVARCHAR(10))--填充数据INSERT tbSELECT TOP 26 col1=CHAR(64+ROW_NUMBER()OVER(ORDER BY GETDATE())) FROM sys.objects--创建分页的存储过程IF OBJECT_ID('[sp_page]') IS NOT NULL DROP PROC [sp_page]GOCREATE PROC sp_page@tablename NVARCHAR(50),--表名@pagerow INT=0,--每页显示的行数(0为全部)@pagenow INT=1, --要显示第几页(默认为)@ordercol VARCHAR(50)=NULL, --排序字段@order BIT=0 --排序规则0顺序,1倒序ASDECLARE @s NVARCHAR(MAX)SET @s='WITH t AS ( SELECT rownum=ROW_NUMBER()OVER(ORDER BY '+ISNULL(@ordercol,'GETDATE()')+ CASE @order WHEN 1 THEN ' desc' ELSE ' asc' END+'), * FROM '+@tablename +')SELECT *FROM t'IF @pagerow>0SET @s=@s+'WHERE rownum BETWEEN '+LTRIM(@pagerow*(@pagenow-1)+1)+' AND '+LTRIM(@pagerow*@pagenow)EXEC(@s)GO--返回tb表每页行第页的记录EXEC sp_page 'tb',6,2/*rownum Col-------------------- ----------7 G8 H9 I10 J11 K12 L (6 行受影响)*/EXEC sp_page 'tb',6,2,'col',2/*rownum Col-------------------- ----------7 T8 S9 R10 Q11 P12 O (6 行受影响)*/--返回所有记录EXEC sp_page 'tb'/*rownum Col-------------------- ----------1 A2 B3 C4 D5 E6 F7 G8 H9 I10 J11 K12 L13 M14 N15 O16 P17 Q18 R19 S20 T21 U22 V23 W24 X25 Y26 Z (26 行受影响)*/ 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/09/22/4580094.aspx
BEGIN
PRINT N'删除存储过程 sp_Pages'
DROP PROCEDURE sp_Pages
END
GO
PRINT N'生成存储过程 sp_Pages'
GO
CREATE PROCEDURE sp_Pages
@Table nvarchar(4000), -- 表名
@Where nvarchar(4000) = N'', -- 条件
@PageSize int, -- 每页的大小(行数)
@Page int, -- 当前页序号
@FdShow nvarchar (4000) = N'', -- 要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000) = N'', -- 排序字段列表
@RecordSum int output -- 记录总数
AS
IF @FdShow IS NULL OR LTRIM(RTRIM(@FdShow)) = ''
BEGIN
SET @FdShow = '*'
END
SET @Where = ISNULL(@Where,'')
SET @Where = LTRIM(RTRIM(@Where))
IF @Where <> ''
BEGIN
IF UPPER(SUBSTRING(@Where,1,5)) <> 'WHERE'
BEGIN
SET @Where = 'WHERE 1 = 1 ' + @Where
END
END
IF @PageSize > 0
BEGIN
SET @FdOrder = NULLIF(LTRIM(RTRIM(@FdOrder)),'')
SET @FdOrder = ISNULL(@FdOrder,'id')
END
SET @FdOrder = LTRIM(RTRIM(@FdOrder))
IF @FdOrder <> ''
BEGIN
IF UPPER(SUBSTRING(@FdOrder,1,8)) <> 'ORDER BY'
BEGIN
SET @FdOrder = 'ORDER BY ' + @FdOrder
END
END
DECLARE @SqlQuery NVARCHAR(MAX)
DECLARE @SqlCount NVARCHAR(MAX)
-- 获取记录总数
set @SqlCount = 'select @RecordSum = count(*) from ' + @Table + ' ' + @Where
exec sp_executesql @SqlCount, N'@RecordSum int output', @RecordSum output
IF @RecordSum = 0
SET @Page = 0
ELSE IF (@RecordSum - 1) / @PageSize + 1 < @Page
SET @Page = (@RecordSum - 1) / @PageSize + 1
IF @PageSize = -1
BEGIN
SET @SqlQuery = '
SELECT ' + @FdShow + '
FROM '+@Table+' '+ @Where
END
ELSE
BEGIN
SET @SqlQuery = '
SELECT ' + @FdShow + ',RowNumber
FROM (
SELECT ' + @FdShow + ',ROW_NUMBER() OVER( '+ @FdOrder +') AS RowNumber
FROM '+@Table+' '+ @Where +') AS RowNumberTableSource
WHERE RowNumber BETWEEN ' + CAST(((@Page - 1) * @PageSize+1) AS VARCHAR) + '
AND ' + CAST((@Page * @PageSize) AS VARCHAR)
END
EXECUTE(@SqlQuery)
RETURN 0
GO
GRANT EXEC ON sp_Pages TO PUBLIC
GO
SET @pagesize = 5;
SET @pagenum = 2;WITH SalesCTE AS
(
SELECT ROW_NUMBER() OVER(ORDER BY qty, empid) AS rownum,
empid, mgrid, qty
FROM dbo.Sales
)
SELECT rownum, empid, mgrid, qty
FROM SalesCTE
WHERE rownum > @pagesize * (@pagenum-1)
AND rownum <= @pagesize * @pagenum
ORDER BY rownum;
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC aspx里面只需给SQL传入pageid和条数即可。 CSDN上还有个存储过程实现分页的代码:
ALTER PROCEDURE news_Showlist
(
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int , -- 页尺寸
@PageIndex int , -- 页码
@strWhere varchar(1500), -- 查询条件(注意: 不要加where)
@Sort varchar(255) --排序的方法)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型if @Sort = 'desc'
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName
+ ' where ' + @strWhere + ' ' + @strOrder
end
else
begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName
+ ' '+ @strOrder
end
--如果是第一页就执行以上代码,这样会加快执行速度
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
exec (@strSQL)
RETURN