一個通用的數據分頁的存儲過程
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
