常用存储过程分页1

**************************************************************************************
分页存储过程
*********************************************************************************** 
CREATE PROCEDURE dbo.comhireinfofiev
       @iPageCount int OUTPUT,   --总页数
       @iPage int,               --当前页号
       @iPageSize int,       --每页记录数
       @tablename varchar(200),
       @textname varchar(200),
       @textname1  varchar(200),
       @searchs varchar(200),
       @orders varchar(200)
      
    as
    set nocount on
    begin
       --创建临时表
       create table #t (ID int IDENTITY,   --自增字段
  fievid int,
  job nvarchar(50),
  zhiwei nvarchar(50),
  loginname nvarchar(100),
  number int,
  categoryid nvarchar(50),
  regdate datetime,
  CoType int,
  comID int,
  Title nvarchar(100))

       --向临时表中写入数据
     
declare @sqlstr varchar(2000)
set @sqlstr = 'select ' + @textname + ' from '+ @tablename
set @sqlstr = @sqlstr + ' where ' + @searchs
set @sqlstr = @sqlstr +' order by ' + @orders
exec( 'insert into #t(' + @textname1 + ')' +@sqlstr)
 
       --取得记录总数
       declare @iRecordCount int
       set @iRecordCount = @@rowcount

       --确定总页数
       IF @iRecordCount%@iPageSize=0
          SET @iPageCount=CEILING(@iRecordCount/@iPageSize)
       ELSE
          SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
 
       --若请求的页号大于总页数，则显示最后一页
       IF @iPage > @iPageCount
          SELECT @iPage = @iPageCount

       --确定当前页的始末记录
       DECLARE @iStart int    --start record
       DECLARE @iEnd int      --end record
       SELECT @iStart = (@iPage - 1) * @iPageSize
       SELECT @iEnd = @iStart + @iPageSize + 1

       --取当前页记录   
       select * from #t where ID>@iStart and ID<@iEnd

       --删除临时表
       DROP TABLE #t

       --返回记录总数
       return @iRecordCount
    end
GO
 ******************************************************************************
调用方法asp
**********************************************************************************
DIM pagenow,pagesize,pagecount,recordcount
DIM MyCommmore,MyRst
pagenow = Request("page")
tablename="comhireinfo"   '打开表名
textname="[id],[job],[zhiwei],[loginname],[number],[categoryid],[regdate],[TH_CoType],[TH_comID]" '打开表字段
textname1="fievid,job,zhiwei,loginname,number,categoryid,regdate,CoType,comID" '插入临时表字段(固定不变)
searchs="TH_key=1 and TH_comID>0 and TH_CoType=1"
orders=" regdate desc,id desc"  '排序方式
'用于验证自然数
If Not IsNumeric(Request("page")) Or IsEmpty(Request("page")) Or Request("page") <=0 Then
pagenow=1
Else
pagenow=Int(Abs(Request("page")))
End if

pagesize = 20

Set MyCommmore = Server.CreateObject("ADODB.Command")
with MyCommmore
.ActiveConnection = conn          'conn是数据库连接字串
.CommandText      = "comhireinfofiev"     '指定存储过程名
.CommandType      = 4                 '表明这是一个存储过程
.Prepared         = true              '要求将SQL命令先行编译
'返回值(记录总量)
.Parameters.Append .CreateParameter("RETURN",2,4)
'出参(总页数)
.Parameters.Append .CreateParameter("@iPageCount",3,2)
'入参(当前页号)
.Parameters.append .CreateParameter("@iPage",3,1,4,pagenow)
'入参(
.Parameters.append .CreateParameter("@iPageSize",3,1,4,pagesize)
.Parameters.append .CreateParameter("@tablename",200,1,40,tablename)
.Parameters.append .CreateParameter("@textname",200,1,100,textname)
.Parameters.append .CreateParameter("@textname1",200,1,100,textname1)
.Parameters.append .CreateParameter("@searchs",200,1,200,searchs)
.Parameters.append .CreateParameter("@orders",200,1,40,orders)

Set MyRst = .Execute
end with
if MyRst.state = 0 then        '未取到数据，MyRst关闭
recordcount = -1
else
MyRst.close    '注意：若要取得参数值，需先关闭记录集对象
recordcount = MyCommmore(0)
pagecount   = MyCommmore(1)

if cint(pagenow)>=cint(pagecount) then pagenow=pagecount
end if
Set MyCommmore = Nothing
if recordcount = 0 then
Response.Write "没有任何记录"
elseif recordcount > 0 then
MyRst.open
end if