MS-SQL分页函数+ASP - ASP教程

(

@pageIndex int = 1 -- 当前页码0 表示返回所有符合内容

,@pageSize int = 10 -- 页尺寸

,@SID nvarchar(30) = '' -- 主键字段

,@strGetField nvarchar(1000) = '*' -- 需要返回的列

,@strTableName nvarchar(30) = '' --表名

,@strWhere nvarchar(2000) = '' -- 查询条件(注意: 条件中要带where)

,@strOrderBy nvarchar(300) = '' -- 排序

--,@MemberID int=0 --会员ID

)

AS

SET NOCOUNT ON

DECLARE @strSQL nvarchar(4000)

DECLARE @startPos int

DECLARE @endPos int

DECLARE @num int

SET @startPos=@pageSize*(@pageIndex-1)+1

SET @endPos=@startPos+@pageSize-1

--页大小*(页数-1)

SET @num = @pageSize * (@PageIndex - 1)

IF @PageIndex!=0

BEGIN

IF @strWhere != ''

SET @strSQL = 'SELECT TOP '+ cast(@pageSize as nvarchar(10)) +' '+ @strGetField +' FROM '+ @strTableName +' WHERE ('+ cast(@SID as nvarchar(30)) +' NOT IN (SELECT TOP '+ cast(@num as nvarchar(20)) +' '+ cast(@SID as nvarchar(30)) +' FROM '+ @strTableName +' where '+@strWhere+' ORDER BY '+ cast(@strOrderBy as nvarchar(255)) +' )) and '+@strwhere+' ORDER BY '+cast(@strOrderBy as varchar(255)) + ''

else

SET @strSQL = 'SELECT TOP '+ cast(@pageSize as nvarchar(10)) +' '+ @strGetField +' FROM '+ @strTableName +' WHERE ('+ cast(@SID as nvarchar(30)) +' NOT IN (SELECT TOP '+ cast(@num as nvarchar(20)) +' '+ cast(@SID as nvarchar(30)) +' FROM '+ @strTableName +' ORDER BY '+ cast(@strOrderBy as nvarchar(255)) +' )) ORDER BY '+ cast(@strOrderBy as varchar(255)) + ''

--SET @strSQL = 'SELECT TOP 页大小 * FROM Users WHERE (ID NOT IN (SELECT TOP (页大小*(当前页-1)) ID FROM Users ORDER BY ID DESC )) ORDER BY ID DESC'

END

ELSE

BEGIN

IF @strWhere != ''

SET @strSQL = 'select '+ @strGetField +' from ' + @strTableName + ' where '+ @strWhere +' order by '+ @strOrderBy

ELSE

SET @strSQL = 'select '+ @strGetField +' from ' + @strTableName + ' order by '+ @strOrderBy

END

--print(@strSQL)

EXEC(@strSQL)

GO

第二个存储过程

CREATE PROCEDURE [dbo].[USP_GetTotal]

(

@strTableName nvarchar(30) = '',

@strWhere nvarchar(2000) = '' -- 查询条件(注意: 条件中要带where)

)

AS

SET NOCOUNT OFF

DECLARE @strSQL nvarchar(2500)

IF @strWhere != ''

SET @strSQL = 'select count(*) as Total from ['+ @strTableName +'] where '+ @strWhere

ELSE

SET @strSQL = 'select count(*) as Total from ['+ @strTableName +']'

EXEC(@strSQL)

GO

下面是调用这个存储过程的两个函数。可以放到一个包含文件里

,注意，下面的函数里面使用的一些变量，是全局的变量，不是参数传入的，所以需要在调用函数钱，给全局变量赋值。

<%

'取记录总数存储过程

public function getDataRowCount(strTableName, strWhere)

dim maxCount

dim myobj

dim rsCount

maxCount = 0

Set myobj = Server.CreateObject("ADODB.Command")

with myobj

.ActiveConnection = conn

.CommandText = "USP_GetTotal"

.CommandType = 4

.Prepared = true

.Parameters.append .CreateParameter("@strTableName", 200, 1, 30, strTableName)

.Parameters.append .CreateParameter("@strWhere", 200, 1, 2000, strWhere)

Set rsCount = .Execute

end with

Set myobj = Nothing

maxCount = rsCount("Total")

rsCount.close:Set rsCount = Nothing

getDataRowCount = maxCount

end function

'单表分页存储过程

sub getDataRS()

dim obj

Set obj = Server.CreateObject("ADODB.Command")

with obj

.ActiveConnection = conn

.CommandText = "USP_GetList"

.CommandType = 4

.Prepared = true

.Parameters.append .CreateParameter("@pageIndex", 3, 1, 4, iPageIndex)

.Parameters.append .CreateParameter("@pageSize", 3, 1, 4, iPageSize)

.Parameters.append .CreateParameter("@SID", 200, 1, 30, SID) ' 2000

.Parameters.append .CreateParameter("@strGetField", 200, 1, 1000, strGetField)

.Parameters.append .CreateParameter("@tableName", 200, 1, 30, strTableName)

.Parameters.append .CreateParameter("@strWhere", 200, 1, 2000, strWhere)

.Parameters.append .CreateParameter("@strOrderBy", 200, 1, 300, strOrderBy)

Set rstobj = .Execute

end with

Set obj = Nothing

end sub

%>

下面是调用这组分页存储过程的实例

'#############定义分页存储过程所要使用的变量##################################################

'1.定义变量，给部分变量设定初始值

dim iPageIndex, iPageSize, iRowCount

dim strWherem, strTableName, SID, strGetField, strWhere, strOrderBy

iPageSize = 12 '一页内显示的记录条数

strTableName = "dataTable" '表名

SID = "id" '主键名

strGetField = "id, field1,field2,field3"

'要显示的字段名

strWhere = "where1=1 and where2=2"

'where子句

strOrderBy = "id desc" '排序

'2,执行getPageInf() 得到(1)iPageIndex:当前页数,(2)iRowCount：总记录数,(3)iPageCount：总页数

Call getPageInfo() 'include_gb/page.asp

'3,先定义rsTobj，再执行getDataRS().sub getDataRS()使用上面定义的表明，字段，where,页数，总记录数语句等，去访问存储过程，并把结果付给rsTobj(集合)

dim rsTobj

Call getDataRS()

'4,rsTobj得到了返回数据，开始循环输出，写到过程里，方便调用

sub showProducts()

if rsTobj.eof Then

response.Write("暂时没有数据")

else

do while not rsTobj.eof

Call showProdTable(rsTobj("id"),rsTobj("NewsName"),rsTobj("Pictures"),rsTobj("abstract"), rsTobj("AddTime"))

rsTobj.moveNext

loop

end if

CloseRS(rsTobj) '关闭链接 include_gb/connSiteData.asp

end sub

'###############################################################

（编辑：温州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!