无为清净楼资源网 Design By www.qnjia.com
CREATE PROCEDURE GoalerPageSp
@IntPageSize int,
@IntCurrPage int,
@strFields nvarchar(2000),
@strTable varchar(200),
@strWhere varchar(800),
@strOrderType varchar(200),
@strKeyField varchar(50)
AS
SET NOCOUNT ON
DECLARE @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+' '+@tmpOrder
ELSE
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+' '+@tmpOrder
EXEC(@tmpSQL)
GO
调用方法:
IntPageSize=20
strTable=" [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=1
END IF
IF strWhere<>"" THEN
tmpWhere=" WHERE "&strWhere
ELSE
tmpWhere=""
END IF
IF(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 IF
ELSE
RecCount=Conn.Execute("SELECT COUNT(*) FROM "&strTable&tmpWhere)(0)
SESSION("RecCount")=RecCount
SESSION("strWhere")=strWhere
END IF
IF(RecCount MOD IntPageSize <>0)THEN
IntPageCount=INT(RecCount/IntPageSize)+1
ELSE
IntPageCount=RecCount/IntPageSize
END IF
SET 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=Null
END IF
SET RS=NOTHING
有用的朋友请自己慢慢调试吧,总记录是用ASP来取的,存储在SESSION里边,如果每次都统计一次总记录,将会非常费时,当然,如果你想在存储过程里来取总记录和总页数然后返回也是可以的,下边是代码:
--获取记录总数--
SET @tmpSQL='SELECT @getRecordCounts=COUNT('+@strKeyField+') FROM '+@strTable+@tmpWhere
EXEC sp_executesql @tmpSQL,N'@getRecordCounts int output',@getRecordCounts OUTPUT
--获取总页数--
SET @tempFolatNumber=@getRecordCounts%@IntPageSize
IF @getRecordCounts<=@IntPageSize
SET @getPageCounts=1
ELSE
BEGIN
IF @tempFolatNumber != 0
SET @getPageCounts=(@getRecordCounts/@IntPageSize)+1
ELSE
SET @getPageCounts=(@getRecordCounts/@IntPageSize)
END
别忘了返回定义参数:
@getRecordCounts int output,--返回总记录
@getPageCounts int output--返回总页数
@IntPageSize int,
@IntCurrPage int,
@strFields nvarchar(2000),
@strTable varchar(200),
@strWhere varchar(800),
@strOrderType varchar(200),
@strKeyField varchar(50)
AS
SET NOCOUNT ON
DECLARE @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+' '+@tmpOrder
ELSE
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+' '+@tmpOrder
EXEC(@tmpSQL)
GO
调用方法:
IntPageSize=20
strTable=" [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=1
END IF
IF strWhere<>"" THEN
tmpWhere=" WHERE "&strWhere
ELSE
tmpWhere=""
END IF
IF(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 IF
ELSE
RecCount=Conn.Execute("SELECT COUNT(*) FROM "&strTable&tmpWhere)(0)
SESSION("RecCount")=RecCount
SESSION("strWhere")=strWhere
END IF
IF(RecCount MOD IntPageSize <>0)THEN
IntPageCount=INT(RecCount/IntPageSize)+1
ELSE
IntPageCount=RecCount/IntPageSize
END IF
SET 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=Null
END IF
SET RS=NOTHING
有用的朋友请自己慢慢调试吧,总记录是用ASP来取的,存储在SESSION里边,如果每次都统计一次总记录,将会非常费时,当然,如果你想在存储过程里来取总记录和总页数然后返回也是可以的,下边是代码:
--获取记录总数--
SET @tmpSQL='SELECT @getRecordCounts=COUNT('+@strKeyField+') FROM '+@strTable+@tmpWhere
EXEC sp_executesql @tmpSQL,N'@getRecordCounts int output',@getRecordCounts OUTPUT
--获取总页数--
SET @tempFolatNumber=@getRecordCounts%@IntPageSize
IF @getRecordCounts<=@IntPageSize
SET @getPageCounts=1
ELSE
BEGIN
IF @tempFolatNumber != 0
SET @getPageCounts=(@getRecordCounts/@IntPageSize)+1
ELSE
SET @getPageCounts=(@getRecordCounts/@IntPageSize)
END
别忘了返回定义参数:
@getRecordCounts int output,--返回总记录
@getPageCounts int output--返回总页数
无为清净楼资源网 Design By www.qnjia.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
无为清净楼资源网 Design By www.qnjia.com
暂无评论...
更新日志
2024年11月15日
2024年11月15日
- 廖也欧《面朝大海》[Hi-Res][24bit 48kHz][FLAC/分轨][170.14MB]
- s13T1夺冠五人名单都有谁 s13T1夺冠五人名单一览
- 英雄联盟T1战队队长都有谁 T1战队所有队长介绍
- skt历届战队成员都有哪些 skt历届战队成员名单盘点
- 妙音唱片《大热唱片3》[WAV+CUE]
- 费玉清《跟着地球旋转》滚石时代经典复刻[正版原抓WAV+CUE]
- 罗文甄妮-射雕英雄传(AMCD)(限量版)[WAV+CUE]
- 《巫师4》定档2025再添佐证:参演人员曝光 老将回归
- 辣眼睛 美女COS《黑神话:悟空》比基尼版金池长老
- 外媒称PS5pro违背承诺:《蜘蛛侠2》根本没法4K60帧
- 令晴 Lynn《The Make》[320K/MP3][44.47MB]
- 令晴 Lynn《The Make》[Hi-Res][24bit 48kHz][FLAC/分轨][295.42MB]
- 雷婷《移情别恋HQⅡ》头版限量编号[低速原抓WAV+CUE][1G]
- FUNDAMENTAL.1989-感觉号渡轮【SONY】【WAV+CUE】
- 上山安娜.1986-上山安娜【EMI百代】【WAV+CUE】