这是个带参数的分页存储过程,不过在查询分析器中执行错误。
use xindedatabase
go
create proc result @title varchar(20),@content varchar(20),@keyword varchar(20),@page int
as
select top 2 * from news where
(id not in
(select top @page=@page*2 as int) id from news
where (id
in
(select id from news
where
(title like '%'+@title+'%' and content like '%'+@content+'%' and keyword like '%'+@keyword+'%')
)
)
)
go
帮我看看错在哪儿!谢谢!
错误是这样的:
服务器: 消息 170,级别 15,状态 1,过程 result,行 5
第 5 行: '@page' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 result,行 12
第 12 行: ')' 附近有语法错误。
use xindedatabase
go
create proc result @title varchar(20),@content varchar(20),@keyword varchar(20),@page int
as
select top 2 * from news where
(id not in
(select top @page=@page*2 as int) id from news
where (id
in
(select id from news
where
(title like '%'+@title+'%' and content like '%'+@content+'%' and keyword like '%'+@keyword+'%')
)
)
)
go
帮我看看错在哪儿!谢谢!
错误是这样的:
服务器: 消息 170,级别 15,状态 1,过程 result,行 5
第 5 行: '@page' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 result,行 12
第 12 行: ')' 附近有语法错误。
(select top @page=@page*2 as int) id from news
这句就有错,要用动态语句才能返回指定的行,没有你的那种写法。
@title varchar(20),
@content varchar(20),
@keyword varchar(20),
@page int
asset @page=@page*2
declare @sql varchar(8000)
set @sql='select top 2 * from news where (id not in(select top '+cast(@page as varchar)+' as int) id from news
where (id in (select id from news where (title like ''%'+@title+'%'' and content like ''%'+@content+'%'' and keyword like ''%'+@keyword+'%''))))'
exec(@sql)
go
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'as' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: ')' 附近有语法错误。
是不是那个 select top 后面不能加参数,只能加常量啊?
@title varchar(20),
@content varchar(20),
@keyword varchar(20),
@page int
asset @page=@page*2
declare @sql varchar(8000)
set @sql='select top 2 * from news where id not in
(select top '+cast(@page as varchar(100))+' id from news
where id in (select id from news where title like ''%'+@title+'%'' and content like ''%'+@content+'%'' and keyword like ''%'+@keyword+'%''))'
exec(@sql)
go
@title varchar(20),
@content varchar(20),
@keyword varchar(20),
@page int
asset @page=@page*2
declare @sql varchar(8000)
set @sql='select top 2 * from news where id not in(select top '+cast(@page as varchar)+' id from news)
and id in(select id from news where title like ''%'+@title+'%'' and content like ''%'+@content+'%'' and keyword like ''%'+@keyword+'%'')'
exec(@sql)
go
@title varchar(20),
@content varchar(20),
@keyword varchar(20),
@page int
as
select top 2 * from news where (title like '%'+@title+'%' and content like '%'+@content+'%' and keyword like '%'+@keyword+'%')
and (id not in
(select top @page=@page*2 id from news where
(id in (select id from news where (title like '%'+@title+'%' and content like '%'+@content+'%' and keyword like '%'+@keyword+'%')
)
)
)
@title varchar(20),
@content varchar(20),
@keyword varchar(20),
@page int
asset @page=@page*2
declare @sql varchar(8000)
set @sql='select top 2 * from news where id not in(select top '+cast(@page as varchar)+' id from news)
and id in(select id from news where title like ''%'+@title+'%'' and content like ''%'+@content+'%'' and keyword like ''%'+@keyword+'%'')'
exec(@sql)
go
采用这种方式系统不出错,但是功能没有实现,就是说没有查出相应的数据,得到的都是同一个数据,不是分页的。