---------------------------------------------------------
ASP
<%
sql = "select * from [table] where 条件"
sql2 = "select count(1) from [table]"
sql = "Exec bbs_ProcIndex '"&sql&"','"&sql2&"',"&page&" ,"&PageSize&""
set rs = conn.execute(sql)
if rs.eof then
rCount = rs(0)
set rs1 = rs.NextRecordset
PageCount=CInt(rCount\PageSize+1)
……
……
end if
CREATE PROCEDURE sp_page(
@tb varchar(50), --表名
@collist varchar(800), --要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@where varchar(800), --where查询条件 (不带WHERE)
@orderby varchar(800), -- order by 条件
@records int OUTPUT --总记录数
)
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @where is null or rtrim(@where)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@where+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@where+') '--原本没有条件而加上此条件
END
Set @sql='select @records = count(*) from '+@tb+@where2
EXEC sp_executesql @sql,N'@records int OUTPUT',@records OUTPUT--计算总记录数
--set @pages=CEILING((COUNT(*)+0.0) / @pagesize)
--最多只能查询10页
IF @page>100
SET @page=100
IF @page=1
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+ @where2+' '+@orderby
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@tb+@where1+'ID NOT IN (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' ID FROM '+@tb+@where2+' '+@orderby+') '+@orderby
EXEC(@sql)