求帮看一小段SQL存储过程语句，有语法错误
- SQL code
ALTER proc [dbo].[selectmymessage] @a varchar, @pageSize int, @pageIndex intasdeclare @sql nvarchar(2000)if(@pageIndex<2)beginset @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='+@aendelsebeginset @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'endexec sp_executesql @sql
[解决办法]
- SQL code
ALTER proc [dbo].[selectmymessage] @a varchar, @pageSize int, @pageIndex intasdeclare @sql nvarchar(2000)if(@pageIndex<2)beginset @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''''endelsebeginset @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'endexec sp_executesql @sql---@a 的附近有错误 上面是修改好的
[解决办法]
- SQL code
ALTER proc [dbo].[selectmymessage] @a varchar, @pageSize int, @pageIndex intasdeclare @sql nvarchar(2000)if(@pageIndex<2)beginset @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='''+@a+''''endelsebeginset @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
[解决办法]
- SQL code
alter proc [dbo].[selectmymessage]( @a varchar(1000), @pageSize int, @pageIndex int )asbegin 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 @sqlend
[解决办法]
- SQL code
create proc [dbo].[selectmymessage] @a varchar, @pageSize int, @pageIndex intasdeclare @sql nvarchar(2000)if(@pageIndex<2) begin set @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook where name='+@a endelse 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' endexec sp_executesql @sql