一个SQL存储过程分页和ASP调用

存储过程:

CREATE PROCEDURE Sp_Page
@IntPageSize int,--页大小
@IntCurrPage int,--页码
@strFields nvarchar(1000),--需要列出的字段
@strTable varchar(100),--查询表或视图
@strWhere varchar(400),--查询条件
@strOrderType varchar(200),--排序类型，0升序，1降序
@strOrderBy varchar(100),--排序条件
@strKey  varchar(50),--主键
@getRecordCounts int output,--返回总记录
@getPageCounts int output--返回总页数
AS
SET NOCOUNT ON
DECLARE @tmpSQL nvarchar(2000)--存放动态SQL语句
DECLARE @tmpWhere varchar(400)--临时存放查询条件
DECLARE @tmpAndWhere varchar(400)--用于第N(>1)页上边的查询条件
DECLARE @tmpOrder varchar(200)--排序条件
DECLARE @tmpD_X varchar(2)-- < OR >
DECLARE @tmpMin_MAX varchar(3)--MIN OR MAX与上对应
DECLARE @Moder float--总记录/页大小的余数，计算总页数时使用到
--设置条件--
IF @strWhere IS NULL OR RTRIM(@strWhere)=''
BEGIN --没有查询条件
  SET @tmpWhere=''
  SET @tmpAndWhere=''
END
ELSE
BEGIN --有查询条件
  SET @tmpWhere=' WHERE '+@strWhere
  SET @tmpAndWhere=' AND '+@strWhere
END

--设置排序--
IF @strOrderType != 0
BEGIN--降序
  SET @tmpD_X = '<'
  SET @tmpMin_MAX = 'MIN'
  IF @strOrderBy IS NULL OR RTRIM(@strOrderBy)=''
   SET @tmpOrder=' ORDER BY ' +@strKey+ ' DESC'--默认按主键降序
  ELSE
   SET @tmpOrder=' ORDER BY '+@strOrderBy+','+@strKey+ ' DESC'
END
ELSE
BEGIN --升序
  SET @tmpD_X = '>'
  SET @tmpMin_MAX = 'MAX'
  IF @strOrderBy IS NULL OR RTRIM(@strOrderBy)=''
   SET @tmpOrder=' ORDER BY ' +@strKey+ ' ASC'--默认按主键升序
  ELSE
   SET @tmpOrder=' ORDER BY '+@strOrderBy+','+@strKey+ ' ASC'
END

--获取记录总数--
SET @tmpSQL='SELECT @getRecordCounts=COUNT('+@strKey+') FROM '+@strTable+@tmpWhere
EXEC sp_executesql @tmpSQL,N'@getRecordCounts int output',@getRecordCounts OUTPUT

--获取总页数--
SET @Moder=@getRecordCounts%@IntPageSize
IF @getRecordCounts<=@IntPageSize
SET @getPageCounts=1
ELSE
BEGIN
IF @Moder != 0
  SET @getPageCounts=(@getRecordCounts/@IntPageSize)+1
ELSE
  SET @getPageCounts=(@getRecordCounts/@IntPageSize)
END

--取得记录的SQL查询--
IF @IntCurrPage=1
Set @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrder
ELSE
SET @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' WHERE ('+@strKey+' '+@tmpD_X+' (SELECT '+@tmpMin_MAX+'('+@strKey+') FROM (SELECT TOP '+CAST(@IntPageSize*(@IntCurrPage-1) AS VARCHAR)+' '+@strKey+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrder+') AS T))'+@tmpAndWhere+' '+@tmpOrder

EXEC(@tmpSQL)
GO

ASP调用:

CurrPage=Request.QueryString("Page")
If CurrPage<>"" and Isnumeric(CurrPage) Then
CurrPage=clng(CurrPage)  
If CurrPage<1 Then CurrPage=1                  
Else
CurrPage=1
End If

IntPageSize=10
strFields=" id,title,content "
strTable=" [Table] "
strKey="id"
strWhere=" classid=1 "  '这里不需要带where
strOrderType=1
strOrderBy="istop desc"  '这里不要带order by

strConn="DRIVER={SQL Server};SERVER=127.0.0.1; UID=sa;PWD=123456;DATABASE=testTable"
Conn.Open strConn
Set Cmd=Server.CreateObject("Adodb.Command")
Cmd.CommandType=4
Set Cmd.ActiveConnection=Conn
Cmd.CommandText="Sp_Page"
Cmd.Parameters.Append Cmd.CreateParameter("@IntPageSize",4,1,4,IntPageSize)
Cmd.Parameters.Append Cmd.CreateParameter("@IntCurrPage",4,1,4,CurrPage)
Cmd.Parameters.Append Cmd.CreateParameter("@strFields",200,1,1000,strFields)
Cmd.Parameters.Append Cmd.CreateParameter("@strTable",200,1,100,strTable)
Cmd.Parameters.Append Cmd.CreateParameter("@strWhere",200,1,400,strWhere)
Cmd.Parameters.Append Cmd.CreateParameter("@strOrderType",4,1,4,strOrderType)
Cmd.Parameters.Append Cmd.CreateParameter("@strOrderBy",200,1,200,strOrderBy)
Cmd.Parameters.Append Cmd.CreateParameter("@strKey",200,1,50,strKey)
Cmd.Parameters.Append Cmd.CreateParameter("@getRecordCounts",4,2)
Cmd.Parameters.Append Cmd.CreateParameter("@getPageCounts",4,2)
Cmd.Execute()
TotalRecord=Cmd("@getRecordCounts")
PageCount=Cmd("@getPageCounts")

Set Rs=Cmd.Execute()
strA=rs("ID")
strB=rs("BB")