日期:2014-05-17  浏览次数:20495 次

求帮看一小段SQL存储过程语句,有语法错误
SQL code

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



------解决方案--------------------
SQL code

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 的附近有错误  上面是修改好的

------解决方案--------------------
SQL code

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'
END

exec sp_executesql @sql

------解决方案--------------------
SQL code

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

------解决方案--------------------
SQL code
create 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 '+@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 @sql