这是自己写的分页sql语句。为什么直接使用@pageSize * @pageIndex会出错呢???
请帮忙修改一下,谢谢!!!if exists (select * from sysobjects where name = 'sp_splitPage')
drop procedure sp_splitPage
go
create procedure sp_splitPage
@pageSize int,--一页显示的行数
@pageIndex int--页索引
as
select top @pageSize * from S_cell where id not in (select top @pageSize * @pageIndex id from S_Cell)
go
请帮忙修改一下,谢谢!!!if exists (select * from sysobjects where name = 'sp_splitPage')
drop procedure sp_splitPage
go
create procedure sp_splitPage
@pageSize int,--一页显示的行数
@pageIndex int--页索引
as
select top @pageSize * from S_cell where id not in (select top @pageSize * @pageIndex id from S_Cell)
go
drop procedure sp_splitPage
go
create procedure sp_splitPage
@pageSize int,--一页显示的行数
@pageIndex int--页索引
as
select top (@pageSize) * from S_cell where id not in (select top (@pageSize * @pageIndex) id from S_Cell)
go
如果是sql2000的话要用动态SQL
谢谢啦!!!
我用的是2005,不过结果也是一样的。。加上括号就是动态SQL吗??
嘿嘿……
----------- ---- ------------ ------------------------------ --------------------
1 ddd f d f00
2 eee a a a00
3 eee f df f00
4 eee sdf asdf sdf00
7 eee sdf fdsa sdf00
12 eee df a df00(6 row(s) affected)if exists (select * from sysobjects where name = 'sp_splitPage')
drop procedure sp_splitPage
go
create procedure sp_splitPage
@pageSize int,--一页显示的行数
@pageIndex int--页索引
as
SELECT TOP(@pageSize) * from book where id not in (select top(@pageSize * @pageIndex) id from book)
GOexec sp_splitPage 3,1id Bno Bclass Bchubanshe Bzuozhe
----------- ---- ------------ ------------------------------ --------------------
4 eee sdf asdf sdf00
7 eee sdf fdsa sdf00
12 eee df a df00(3 row(s) affected)
再请教一个
--分页SQL语句
if exists (select * from sysobjects where name = 'sp_splitPage')
drop procedure sp_splitPage
go
create procedure sp_splitPage
@tableName varchar(200),--表名称
@pageSize int,--一页显示的行数
@pageIndex int,--页索引
@pageCount int output--总页数
as
declare @sql varchar(8000),
@rowCount int
select @rowCount=count(*) from (@tableName)
set @sql='select top '
+ltrim(@pageSize)
+' * from '
+(@tableName)
+' where id not in (select top '
+ltrim(@pageSize * @pageIndex)
+' id from '
+(@tableName)
+')'
exec (@sql)
go我想要返回值该怎么弄???
@tableName已经好了。就是那个output参数有点问题。。
是这样的啊,不过索引时从0开始的,将exec sp_splitPage 3,1
中的1换成0就是想要的结果。
exec select @pageCount=count(*)/@pageSize+1 from @tableName (@sql) 这样???
错了。。
关键是求@rowCount的时候出错了。。
@rowCount int
select @rowCount=count(*) from (@tableName)-->改为:declare @sql varchar(8000),
@dsql Nvarchar(8000)
@rowCount int
set @dsql=N'select @rowCount=count(*) from '+@tableName
exec sp_executesql @dsql,N'@rowCount int output',@rowCount output
drop procedure sp_splitPage
go
create procedure sp_splitPage
@tableName varchar(200),--表名称
@pageSize int,--一页显示的行数
@pageIndex int--页索引
as
declare @sql varchar(8000)
set @sql='select top '
+ltrim(@pageSize)
+' * from '
+(@tableName)
+' where id not in (select top '
+ltrim(@pageSize * @pageIndex)
+' id from '
+(@tableName)
+')'
exec (@sql)
go
--执行存储过程
exec sp_splitPage 'S_Cell',3,2