通用存储过程分页

create procedure pagination3 

@tblName varchar(255), -- 表名 

@strGetFields varchar(1000) = '*', -- 需要返回的列 

@fldName varchar(255)='', -- 排序的字段名 

@PageSize int = 10, -- 页尺寸 

@PageIndex int = 1, -- 页码 

@doCount bit = 0, -- 返回记录总数, 非 0 值则返回

@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 

@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where) 

AS 

declare @strSQL varchar(5000) -- 主语句 

declare @strTmp varchar(110) -- 临时变量 

declare @strOrder varchar(400) -- 排序类型 

if @doCount != 0 

begin 

if @strWhere !='' 

set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 

else 

set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 

end 

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况 

else 

begin 

if @OrderType != 0 

begin 

set @strTmp = '<(select min' 

set @strOrder = ' order by [' + @fldName +'] desc' 

--如果@OrderType不是0，就执行降序，这句很重要！ 

end 

else 

begin 

set @strTmp = '>(select max' 

set @strOrder = ' order by [' + @fldName +'] asc' 

end 

if @PageIndex = 1 

begin 

if @strWhere != '' 

set @strSQL = 'select top ' + str(@PageSize) +' + ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder 

else 

set @strSQL = 'select top ' + str(@PageSize) +' + ' from ['+ @tblName + '] '+ @strOrder 

--如果是第一页就执行以上代码，这样会加快执行速度 

end 

else 

begin 

--以下代码赋予了@strSQL以真正执行的SQL代码 

set @strSQL = 'select top ' + str(@PageSize) +' + ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder 

if @strWhere != '' 

set @strSQL = 'select top ' + str(@PageSize) +' + ' from [' 

+ @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 

+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 

+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' 

+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 

end 

end 

exec (@strSQL) 

GO 

'数据查询条件参数

intPage = Request.QueryString("CurPage")

If intPage = "0" or intPage="" Then

intPage = 1

Else

intPage = int(intPage)

End If

PageSize = page

if price = "high" then

order = "Pd_Market_Price"

orderby=1

Elseif price = "low" then

order = "Pd_Market_Price"

orderby=0

Else

'order ="id"

order="states"

orderby=1

End if

If BigClassid = "" then

if loc1 = "0" then

StrSql = "pd_online=0" 

Else

StrSql = "Pd_online=0 and Pd_Province = " & loc1 & "" 

End if

Else

if loc1 = "0" then

StrSql = "pd_online=0 and Pd_smallClass = '" & Classid & "'"

Else

StrSql = "Pd_online=0 and pd_smallClass = '" & Classid & "' and Pd_Province = " & loc1 & ""

End If 

End If

'记录条数总数

Set Cmd1=server.CreateObject("Adodb.Command") 

Cmd1.ActiveConnection=conn 

Cmd1.CommandText="pagination3" 

Cmd1.CommandType=4 'adCmdStoredProc 

cmd1.prepared=true' 

cmd1.Parameters.Append cmd1.CreateParameter("@tblname",200,1,255,"products")

cmd1.parameters.Append cmd1.CreateParameter("@strgetfields",200,1,1000,"*")

cmd1.parameters.Append cmd1.CreateParameter("@fldname",200,1,255,""&order&"")

cmd1.parameters.Append cmd1.CreateParameter("@Pagesize",3,1,,""&page&"")

cmd1.parameters.Append cmd1.CreateParameter("@pageindex",3,1, ,""&intpage&"")

cmd1.parameters.Append cmd1.CreateParameter("@docount",3,1, ,1)

cmd1.parameters.Append cmd1.CreateParameter("@ordertype",3,1, ,Int(orderby))

cmd1.parameters.Append cmd1.CreateParameter("@strwhere",200,1, 1500,""&strsql&"")

'sql1="select * from products"

'response.write cmd.commandtext

set rs1=Cmd1.execute 

total=rs1("total")

rs1.close 

猜你喜欢