执行EXEC up_Pager 'OA_Autherinfo','datetimes',1,'*',1,1,'' 这时没报错
执行EXEC up_Pager 'OA_Autherinfo','datetimes',1,'*',1,2,'' 报错
就是 当执行第二页时报的错CREATE PROCEDURE Pro_SelectAutherinfo
@table varchar(2000), --表名
@col varchar(50), --要排序的列名
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800), --查询条件
@Pages int output
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_Item int,@total_Page int
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @total_Item=CEILING((COUNT(*)+0.0)'+') FROM '+@table+ @where2
EXEC sp_executesql @sql,N'@total_Item int OUTPUT',@total_Item OUTPUT --计算总条数
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) --计算页总数
set @Pages=@total_Page
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM mailto:'+@table+@where1+@col+'%3E(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM mailto:'+@table+@where1+@col+'%3C(select MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--print @sql
EXEC(@sql)
GO
报服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'mailto:' 附近有语法错误。
CREATE PROCEDURE Pro_SelectAutherinfo
@table varchar(2000), --表名
@col varchar(50), --要排序的列名
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800), --查询条件
@Pages int output
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_Item int,@total_Page int
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @total_Item=CEILING((COUNT(*)+0.0)'+') FROM '+@table+ @where2
EXEC sp_executesql @sql,N'@total_Item int OUTPUT',@total_Item OUTPUT --计算总条数
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) --计算页总数
set @Pages=@total_Page
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+@where1+@col+'%3E(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+@where1+@col+'%3C(select MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--print @sql
EXEC(@sql)
GO
http://topic.csdn.net/u/20100203/17/8F916471-597D-481A-B170-83BCEFE3B199.html应一个朋友的要求,贴上收藏的SQL常用分页的办法~~ 表中主键必须为标识列,[ID] int IDENTITY (1,1) 1.分页方案一:(利用Not In和SELECT TOP分页) 语句形式:
SELECT TOP 页记录数量 *
FROM 表名
WHERE (ID NOT IN
(SELECT TOP (每页行数*(页数-1)) ID
FROM 表名
ORDER BY ID))
ORDER BY ID
//自己还可以加上一些查询条件
例:
select top 2 *
from Sys_Material_Type
where (MT_ID not in
(select top (2*(3-1)) MT_ID from Sys_Material_Type order by MT_ID))
order by MT_ID 2.分页方案二:(利用ID大于多少和SELECT TOP分页) 语句形式:
SELECT TOP 每页记录数量 *
FROM 表名
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 每页行数*页数 id FROM 表
ORDER BY id) AS T)
)
ORDER BY ID 例:
SELECT TOP 2 *
FROM Sys_Material_Type
WHERE (MT_ID >
(SELECT MAX(MT_ID)
FROM (SELECT TOP (2*(3-1)) MT_ID
FROM Sys_Material_Type
ORDER BY MT_ID) AS T))
ORDER BY MT_ID 3.分页方案三:(利用SQL的游标存储过程分页)
create procedure SqlPager
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off 4.总结:
其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
建议优化的时候,加上主键和索引,查询效率会提高。 通过SQL 查询分析器,显示比较:我的结论是:
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用
中,mailto:去掉看看
一般来说调试存储过程 都是print @sql出来看看。
print @sql
--EXEC(@sql)
print出来你拼得sql语句。
SELECT TOP 1 * , 5 as total_Item , 5 as total_Page FROM [mailto:OA_Autherinfo] WHERE datetimes %3C(select MIN(datetimes) FROM (SELECT TOP 1 datetimes FROM OA_Autherinfo ORDER BY datetimes DESC) t) ORDER BY datetimes DESC
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'C' 附近有语法错误。
去掉还是不对,
这过程不是我写的,我只是接手,郁闷
mailto
这是什么意思,主要是做什么用得
try:
CREATE PROCEDURE Pro_SelectAutherinfo
@table varchar(2000), --表名
@col varchar(50), --要排序的列名
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800), --查询条件
@Pages int output
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_Item int,@total_Page int
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @total_Item=CEILING((COUNT(*)+0.0)'+') FROM '+@table+ @where2
EXEC sp_executesql @sql,N'@total_Item int OUTPUT',@total_Item OUTPUT --计算总条数
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) --计算页总数
set @Pages=@total_Page
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM [mailto:'+@table+']'+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM [mailto:'+@table+']'+@where1+@col+'<(select MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--print @sql
EXEC(@sql)
GO
CREATE PROCEDURE Pro_SelectAutherinfo
@table varchar(2000), --表名
@col varchar(50), --要排序的列名
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800), --查询条件
@Pages int output
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_Item int,@total_Page int
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @total_Item=CEILING((COUNT(*)+0.0)'+') FROM '+@table+ @where2
EXEC sp_executesql @sql,N'@total_Item int OUTPUT',@total_Item OUTPUT --计算总条数
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) --计算页总数
set @Pages=@total_Page
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM ['+@table+']'+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM ['+@table+']'+@where1+@col+'<(select MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--print @sql
EXEC(@sql)
GO
执行EXEC up_Pager 'OA_Autherinfo','datetimes',1,'*',1,2,'' 报错同CREATE PROCEDURE Pro_SelectAutherinfo不是同一個
@table varchar(2000), --表名
@col varchar(50), --要排序的列名
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800), --查询条件
@Pages int output
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),@total_Item int,@total_Page int
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @total_Item=CEILING((COUNT(*)+0.0)'+') FROM '+@table+ @where2
EXEC sp_executesql @sql,N'@total_Item int OUTPUT',@total_Item OUTPUT --计算总条数
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) --计算页总数 set @Pages=@total_Page
IF @orderby=0SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
PRINT 'SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+@where1+@col+'<(select MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@table+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' , '+ CAST(@total_Item AS varchar) + ' as total_Item' +
' , '+CAST(@total_Page AS varchar) + ' as total_Page' +
' FROM '+@table+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--print @sql
EXEC(@sql)
GO
对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法--邹建 2003.09(引用请保留此信息)--*//*--调用示例
exec p_show '地区资料' exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GOCREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果显示第一页,可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理 select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)GO