【下载文档:  以前写的一个分页存储过程，刚才不小心翻出来的.txt 】

---

以前写的一个分页存储过程，刚才不小心翻出来的
CREATE PROCEDURE GoalerPageSp@IntPageSize int,@IntCurrPage int,@strFields nvarchar(2000),@strTable varchar(200),@strWhere varchar(800),@strOrderType varchar(200),@strKeyField varchar(50)ASSET NOCOUNT ONDECLARE @tmpSQL nvarchar(4000)--存放动态SQL语句DECLARE @tmpWhere varchar(800)DECLARE @tmpAndWhere varchar(800)--用于第N(>1)页上边的查询条件DECLARE @tmpOrder varchar(200)DECLARE @tmpD_X varchar(2)DECLARE @tmpMin_MAX varchar(3)--设置条件--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' SET @tmpOrder=' ORDER BY ' +@strKeyField+ ' DESC' END ELSE BEGIN SET @tmpD_X = '>' SET @tmpMin_MAX = 'MAX' SET @tmpOrder=' ORDER BY ' +@strKeyField+ ' ASC' END --SQL查询--IF @IntCurrPage=1 Set @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrderELSE SET @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' WHERE ('+@strKeyField+' '+@tmpD_X+' (SELECT '+@tmpMin_MAX+'('+@strKeyField+') FROM (SELECT TOP '+CAST(@IntPageSize*(@IntCurrPage-1) AS VARCHAR)+' '+@strKeyField+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrder+') AS T))'+@tmpAndWhere+' '+@tmpOrderEXEC(@tmpSQL)GO调用方法：
IntPageSize=20strTable=" [TableName] " '数据表名称strFields=" Field1,Field2,Field3,Field4 " '需要读取的列名strKeyField="Field1" '主键：这里假设Field1为主键strWhere="" '条件：FieldA='b'strOrderType=1 '排序方式：1为倒序，0为顺序CurrPage=Request.QueryString("Page")IF(CurrPage<>"" And Isnumeric(CurrPage))THEN CurrPage=CLNG(CurrPage) IF(CurrPage<1)THEN CurrPage=1 ELSE CurrPage=1END IFIF strWhere<>"" THEN tmpWhere=" WHERE "&strWhereELSE tmpWhere=""END IFIF(SESSION("RecCount")<>"")THEN IF(SESSION("strWhere")<>strWhere)THEN RecCount=Conn.Execute("SELECT COUNT("&strKeyField&") FROM "&strTable&tmpWhere)(0) SESSION("RecCount")=RecCount SESSION("strWhere")=strWhere ELSE RecCount=SESSION("RecCount") END IFELSE RecCount=Conn.Execute("SELECT COUNT(*) FROM "&strTable&tmpWhere)(0) SESSION("RecCount")=RecCount SESSION("strWhere")=strWhereEND IFIF(RecCount MOD IntPageSize <>0)THEN IntPageCount=INT(RecCount/IntPageSize)+1ELSE IntPageCount=RecCount/IntPageSizeEND IFSET Cmd=Server.CreateObject("Adodb.Command") Cmd.CommandType=4 SET Cmd.ActiveConnection=Conn Cmd.CommandText="GoalerPageSp" 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,2000,strFields)Cmd.Parameters.Append Cmd.CreateParameter("@strTable",200,1,200,strTable)Cmd.Parameters.Append Cmd.CreateParameter("@strWhere",200,1,800,strWhere)Cmd.Parameters.Append Cmd.CreateParameter("@strOrderType",4,1,4,strOrderType)Cmd.Parameters.Append Cmd.CreateParameter("@strKeyField",200,1,50,strKeyField)SET RS=Cmd.Execute()IF RecCount<1 THEN Response.Write("没有记录")ELSE GetRecord=RS.GetRows(IntPageSize) For i=0 To Ubound(GetRecord,2) Response.Write(GetRecord(0,i),GetRecord(1,i),GetRecord(2,i)) '...输出内容 NEXT GetRecord=NullEND IFSET RS=NOTHING有用的朋友请自己慢慢调试吧，总记录是用ASP来取的，存储在SESSION里边，如果每次都统计一次总记录，将会非常费时，当然，如果你想在存储过程里来取总记录和总页数然后返回也是可以的，下边是代码：
--获取记录总数--SET @tmpSQL='SELECT @getRecordCounts=COUNT('+@strKeyField+') FROM '+@strTable+@tmpWhereEXEC sp_executesql @tmpSQL,N'@getRecordCounts int output',@getRecordCounts OUTPUT--获取总页数--SET @tempFolatNumber=@getRecordCounts%@IntPageSizeIF @getRecordCounts<=@IntPageSize SET @getPageCounts=1ELSEBEGIN IF @tempFolatNumber != 0 SET @getPageCounts=(@getRecordCounts/@IntPageSize)+1 ELSE SET @getPageCounts=(@getRecordCounts/@IntPageSize)END别忘了返回定义参数：@getRecordCounts int output,--返回总记录@getPageCounts int output--返回总页数