--分页存储过程
CREATE PROC Common_Page
@RecordCount INT,
@QueryStr NVARCHAR(100)='table1',--表名、视图名、查询语句
@PageSize INT=20, --每页的大小(行数)
@PageCurrent INT=0, --要显示的页 从0开始
@FdShow NVARCHAR (1000)='*', --要显示的字段列表
@IdentityStr NVARCHAR (100)='id', --主键
@WhereStr NVARCHAR (1000)='1=1',
@FdOrder NVARCHAR(100)='desc' --排序 只能取desc或者asc
AS
DECLARE
@sql NVARCHAR(4000)
IF @WhereStr=''
BEGIN
SET @WhereStr ='1=1'
END
SET @WhereStr=' ('+@WhereStr+ ' )'
IF @PageCurrent=0
BEGIN
SET @sql ='SELECT TOP' +CAST(@PageSizeASNVARCHAR(10))+''+@FdShow+ ' from'+@QueryStr+ ' where'+@WhereStr+ ' order by'+@IdentityStr+ ''+@FdOrder
END
ELSE
BEGIN
IF UPPER(@FdOrder)='DESC'
BEGIN
SET@sql='SELECT TOP' +CAST(@PageSizeASNVARCHAR(10))+''+@FdShow+ ' from'+@QueryStr+ ' where'+@WhereStr+ ' and'+@IdentityStr+ '< ( select min('+@IdentityStr+') from (SELECT TOP'+CAST(@PageSize*@PageCurrentASNVARCHAR(10))+''+@IdentityStr+ ' FROM'+@QueryStr+ ' WHERE'+@WhereStr+ ' ORDER BY'+@IdentityStr+ ' DESC) AS t) ORDER BY'+@IdentityStr+ ' DESC'
END
ELSE
BEGIN
SET@sql='SELECT TOP' +CAST(@PageSizeASNVARCHAR(10))+''+@FdShow+ ' from'+@QueryStr+ ' where'+@WhereStr+ ' and'+@IdentityStr+ '> ( select max('+@IdentityStr+') from (select top'+CAST(@PageSize*@PageCurrentASNVARCHAR(10))+''+@IdentityStr+ ' FROM'+@QueryStr+ ' WHERE'+@WhereStr+ ' ORDER BY'+@IdentityStr+ ' ASC) AS t) ORDER BY'+@IdentityStr+ ' ASC'
END
END
--print @sql
EXECUTE sp_executesql@sql
IF(@RecordCountisnullor@RecordCount<0)
BEGIN
DECLARE @tsql NVARCHAR(1000)
SET @tsql=N'SELECT @RecordCount = count(*) FROM'+@QueryStr+ ' WHERE'+@WhereStr
EXEC sp_executesql@tsql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT
SELECT @RecordCount
END
GO
备注:在页面显示也可通过页面显示技术,如分页控件,Ajax异步显示等
sql server 2005/2008里面的分页SQL可以是这样写:
select * from (select *, ROW_NUMBER() Over(Order by 任意字段) AS ID_RANK from 表名) A
where ID_RANK >= 1 and ID_RANK <= 10