小弟不才,想请教各位一个存储过程分页的问题
小弟最近开发一个流量统计系统,涉及数据为千万级别,用了SQL SERVER存储过程做的!现在遇到几个难题想请教各位,真心感谢!
存储过程如下(来源网上某位高手的),我自己改动了下,因为有三个条件是每次查询差不多都用到的,就一并写进去了!
CREATE PROCEDURE GetRecordPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int, -- 页尺寸
@PageIndex int, -- 页码
@OrderType bit, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000), -- 查询条件 (注意: 不要加 where)
@fleString varchar(2000), ---取出的字段
@GetUserId int, --我加的参数 用户ID号
@GetGameId int, --我加的参数 游戏ID号
@GetSiteId int --我加的参数 站点ID号
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型
/***以下部分是我改的
当用户ID不为空时，原始的strwhere（查询条件 不带where的）语句 连接上我加上的条件
if @GetUserId != ' '
set @strWhere = @strWhere + ' and come_userid = ' +@GetUserId
if @GetGameId != ' '
set @strWhere = @strWhere + ' and come_gameid = ' +@GetGameId
if @GetSiteId != ' '
set @strWhere = @strWhere + ' and come_siteid = ' +@GetSiteId
**/
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + '] desc '
end
else
begin
set @strTmp = '> (select max '
set @strOrder = ' order by [ ' + @fldName + '] asc '
end
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fleString + ' 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) + ' '+ @fleString + ' from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ' '
if @strWhere != ' '
set @strTmp = ' where ( ' + @strWhere + ') '
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fleString + ' from [ '
+ @tblName + '] ' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
GO
asp里调试的代码如下：
<!--CONN文件等-->
<%dim page
if request.querystring( "page ") = " " then
page =1
else
page = request.QueryString( "page ")
end if
CreateConn Conn '连接了库
Set cmd = Server.CreateObject( "ADODB.Command ")
cmd.ActiveConnection = Conn '数据库连接字串
cmd.CommandText = "GetRecordPage " '指定存储过程名
cmd.CommandType = 4 '表明这是一个存储过程
cmd.Prepared = true '要求将SQL命令先行编译
cmd.Parameters.append cmd.CreateParameter( "@tblName ",200,1,50, "IPtable " )
cmd.Parameters.append cmd.CreateParameter( "@fldName ",200,1,50, "ID ")
cmd.Parameters.append cmd.CreateParameter( "@PageSize ",3,1,4,20)
cmd.Parameters.append cmd.CreateParameter( "@PageIndex ",3,1,4,page)
cmd.Parameters.append cmd.CreateParameter( "@OrderType ",3,1,4,1)
cmd.Parameters.append cmd.CreateParameter( "@strWhere ",200,1,800, "Id <> 0 ") '查询条件where 中的条件语句
cmd.Parameters.append cmd.CreateParameter( "@fleString ",200,1,800, "id,v_ip,v_year,v_month,v_day,v_time,v_osinfo,(select top 1 gamesname from gametable where id = IPtable.come_gameid) as come_gamename,(select top 1 name from sitetable where id = CE_IPCount.come_siteid and userid = IPtable.come_userid) as come_sitename,pvcount ")
cmd.Parameters.append cmd.CreateParameter( "@GetUserId ",3,1,4,session( "uid "))
cmd.Parameters.append cmd.CreateParameter( "@GetGameId ",3,1,4,0)
cmd.Parameters.append cmd.CreateParameter( "@GetSiteId ",3,1,4,0)
Set rs = cmd.Execute
if rs.state = 0 then
recordcount = -1
else
rs.close
end if
Set cmd = Nothing
rs.open
for i = 1 to 20
Response.write Rs(0)& "----------- "&Rs(1)& "----------- "&Rs(2)& " <br> "
rs.movenext
next
%>
现在报的错误是：
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e07 '
[Microsoft][ODBC SQL Server Driver][SQL Server]将 varchar 值 'Id <> 0 and come_userid = ' 转换为数据类型为 int 的列时发生语法错误。
/test.asp，行 31
望各位达人帮忙看看！跪谢！
[解决办法]
if @GetUserId != ' '
set @strWhere = @strWhere + ' and come_userid = ' +rtrim(@GetUserId)
if @GetGameId != ' '
set @strWhere = @strWhere + ' and come_gameid = ' +rtrim(@GetGameId)
if @GetSiteId != ' '
set @strWhere = @strWhere + ' and come_siteid = ' +rtrim(@GetSiteId)