SQL分页

/*@table varchar(2000), --表名
@col varchar(50), --按该列来进行分页
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800) --查询条件
*/

DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_Item int,@total_Page int ,@pagesize int,@page int,@condition varchar(800),@orderby bit,
@col varchar(50),@table varchar(2000),@collist varchar(800)

/*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 @total_Item=CEILING((COUNT(*)+0.0)'+') FROM PT_HOMEWORK'
SET @pagesize =6
SET @page =2
SET @orderby =0
EXEC sp_executesql @sql,N'@total_Item int OUTPUT',@total_Item OUTPUT
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) --计算页总数
print @total_Page

IF @orderby=0
 SET @sql='SELECT TOP
 '+CAST(@pagesize AS varchar)+' *
 FROM PT_HOMEWORK
 WHERE HOMEWORK_CD > (
    SELECT MAX(HOMEWORK_CD) 
    FROM (
     SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+'HOMEWORK_CD
     FROM PT_HOMEWORK
     ORDER BY HOMEWORK_CD
     ) t
  ) ORDER BY HOMEWORK_CD'
ELSE
 SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' * 
 FROM PT_HOMEWORK
 WHERE HOMEWORK_CD >(
   SELECT  MIN(HOMEWORK_CD)
   FROM (
     SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+'
     HOMEWORK_CD
     FROM PT_HOMEWORK
     ORDER BY HOMEWORK_CD DESC
    ) t
  ) ORDER BY HOMEWORK_CD DESC'
IF @page=1--第一页
 SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' *
  FROM PT_HOMEWORK
  ORDER BY HOMEWORK_CD '+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
 --print @sql
EXEC(@sql)