IF @CONDITION IS NULL OR RTRIM(@CONDITION) = ''
BEGIN
SET @WHERE1=' WHERE '
SET @WHERE2=' '
END
ELSE
BEGIN
SET @WHERE1=' WHERE ('+@CONDITION+') AND '
SET @WHERE2=' WHERE ('+@CONDITION+') '
END
SET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2
IF @RecPages = 0
EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT
ELSE
SELECT @PAGES = @RecPages
IF @ORDERBY=0
SET @SQL= 'SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
@COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+') t) ORDER BY '+@COL
ELSE
SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
@COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+' DESC) t) ORDER BY '+@COL+' DESC'
IF @PAGE=1
SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' END
SET @OUTSQL = @SQL
EXEC(@SQL)
这是以前写的一个分页存储过程.设id为标识
实际上就是
当当前页=1时
select top 页行数 from tb
当前页为n时
select top 页行数 from tb where id>(select max(id) from (select top (n-1)*页行数 id from tb)
比如当前页为5,每页显示10条记录
那么前4页应该显示40条记录, 若以id顺序来排的话, 则第5页显示的记录应该是 id大小排第41至第50的.即取前10条 id>( 前40条记录的最大id)
select top 3 [News].[Unique], [News].[Title], [News].[Create] from [News]
where [News].[Unique] not in
(
select top 30 [News].[Unique] from [News] order by [News].[Create] desc
)
order by [News].[Create] desc
if object_id('testdb') is not null drop table testdb
select ID=identity(int, 1, 1), * into testdb from tablename
select * from testdb
drop table testdb