ALTER proc [dbo].[selectmymessage]
@a varchar,
@pageSize int,
@pageIndex int
as
declare @sql nvarchar(2000)
if(@pageIndex<2)
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='+@a
end
else
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='+@a+
' id>(select max(id) from (
select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook where name='+@a+' order by id) a) where name='+@a+' order by id'
end
exec sp_executesql @sql
ALTER proc [dbo].[selectmymessage]
@a varchar,
@pageSize int,
@pageIndex int
as
declare @sql nvarchar(2000)
if(@pageIndex<2)
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''''
end
else
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''' id>(select max(id) from (
select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook where name='''+@a+''' order by id) a) where name='''+@a+''' order by id'
end
exec sp_executesql @sql
---@a 的附近有错误 上面是修改好的
ALTER proc [dbo].[selectmymessage]
@a varchar,
@pageSize int,
@pageIndex int
as
declare @sql nvarchar(2000)
if(@pageIndex<2)
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''''
end
else
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''' and id>(select max(id) from (
select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook where name='''+@a+''' order by id) a) and name='''+@a+'''order by id'
ENDexec sp_executesql @sql@a 的引号家少了 还有就是条件写错了你可以用下面的方法打印出你拼接的语句 这样很好调试的
declare
@a VARCHAR='a',
@pageSize INT=20,
@pageIndex INT=10
--as
declare @sql nvarchar(2000)
if(@pageIndex<2)
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''''
end
else
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''' and id>(select max(id) from (
select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook where name='''+@a+''' order by id) a) and name='''+@a+'''order by id'
END
PRINT @sql
alter proc [dbo].[selectmymessage]
( @a varchar(1000),
@pageSize int,
@pageIndex int )
as
begin
declare @sql nvarchar(2000)
if(@pageIndex<2)
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''' '
end
else
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''' '
+' and id>(select max(id) from (
select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook where name='''+@a+''' order by id) a)'
end
exec sp_executesql @sql
end
alter proc [dbo].[selectmymessage]
( @a varchar(1000),
@pageSize int,
@pageIndex int )
as
begin
declare @sql nvarchar(2000)
if(@pageIndex<2)
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''' '
end
else
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''' '
+' and id>(select max(id) from (
select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook where name='''+@a+''' order by id) a)'
end
exec sp_executesql @sql
end
@a varchar,
@pageSize int,
@pageIndex int
as
declare @sql nvarchar(2000)
if(@pageIndex<2)
begin
set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='+@a
end
else
begin
set @sql='select top '+@pageSize+' * from gbook where name='''+@a+''' and id>(select max(id) from (
select top '+@pageSize*(@pageIndex-1)+' id from gbook where name='''+@a+'''
order by id) a) where name='''+@a+''' order by id'
end
exec sp_executesql @sql1)@a需要再加两个单引号
2)@pageSize本身就是INT类型,在取TOP得时候不需要转为VARCHAR的