一个通用的数据分页的存储过程
CREATE PROCEDURE sp_page
@tb varchar(20), --表名
@col varchar(20), --按该列来进行分页
@coltype bit, --@col列的类型,0-数字类型,1-字符类型
@collist varchar(800),--要查询出的字段列表
@selecttype int, --查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页
@pagesize int, --每页记录数
@page int, --指定页
@minid varchar(50), --当前最小号
@maxid varchar(50) --当前最大号
AS
DECLARE @sql varchar(8000)
IF @coltype=1
BEGIN
SET @minid=''''+@minid+''''
SET @maxid=''''+@maxid+''''
END
SET @sql=
CASE @selecttype
WHEN 1--前页
THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' WHERE '+@col+'<'+@minid+
' ORDER BY '+@col+' DESC) t ORDER BY '+@col
WHEN 2--后页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+' WHERE '+@col+'>'+@maxid+' ORDER BY '+@col
WHEN 3--首页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' ORDER BY '+@col
WHEN 4--末页
THEN 'SELECT '+@collist+' FROM (SELECT TOP '+CAST(@pagesize AS varchar)+
' '+@collist+' FROM '+@tb+' ORDER BY '+@col+' DESC) t ORDER BY '+@col
WHEN 5--指定页
THEN 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+' WHERE '+@col+' NOT IN'+
'(SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+@col+
' FROM '+@tb+' ORDER BY '+@col+') ORDER BY '+@col
END
EXEC(@sql)
GO
