CREATE procedure pro_pagerArticle
(@pagesize int,
@pageindex int,@sqlwhere varchar(500))
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select id from t_Article + @sqlwhere + order by up desc,addtime desc
select O.id,title,(select bigclassname from t_bigclass b where b.bigclassid = O.bigclassid) bigclassname,
(select smallclassname from t_smallclass c where c.smallclassid = O.smallclassid) smallclassname,
keyword,content,origin,author,addtime,hits,img,wmv,validatestate,up,openleaveword,filedown
from t_article O,@indextable t where O.id=t.nid
and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
end
set nocount off
GO
@sqlwhere 是条件 比如:where bigclassid=2
怎么解决
(@pagesize int,
@pageindex int,@sqlwhere varchar(500))
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select id from t_Article + @sqlwhere + order by up desc,addtime desc
select O.id,title,(select bigclassname from t_bigclass b where b.bigclassid = O.bigclassid) bigclassname,
(select smallclassname from t_smallclass c where c.smallclassid = O.smallclassid) smallclassname,
keyword,content,origin,author,addtime,hits,img,wmv,validatestate,up,openleaveword,filedown
from t_article O,@indextable t where O.id=t.nid
and t.id between @PageLowerBound+1 and @PageUpperBound order by t.id
end
set nocount off
GO
@sqlwhere 是条件 比如:where bigclassid=2
怎么解决
然后用exec sp_executesql N@sql语句执行
exec(@sql)提示必须声明:@indextable变量
提示必须声明:@indextable变量
我几乎都没有用到存储过程
这次真的被卡住了
@indextable
@indextable(nid)
declare @indextable table(id int identity(1,1),nid int)
到底是什么东东
声明一个表变量
insert @indextable(nid) values(5)
向表变量的指定列插入值。
执行
EXEC(@sql)或者EXEC sp_executesql @Sql
那怎么组合起来这句sql
都是提示@indextable 变量未声明
declare @sql varchar(1000)
set @sql = ' insert into @indextable(nid) select id from t_Article '+@sqlwhere
exec @sql
如果这样又可以
declare @sql varchar(1000)
set @sql = ' insert into @indextable(nid) select id from t_Article '
exec @sql