老调试不成功的存储过程，一个堵我心口好久的问题。

[wnglish]

该存储过程老说：“@indextable没有声明”，我明明声明了，晕倒。

CREATE procedure s_Tables_Page
(
@pagesize int,
@pageindex int,
@table varchar(200)
)
as
set nocount on
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
exec ('insert into '+@indextable+'(nid) select id from '+@table+' order by id desc')
exec ('select * from '+@table+' O,'+@indextable+'t where O.id=t.nid and t.id>'+@PageLowerBound+' and t.id<='+@PageUpperBound+' order by t.id')

set nocount off
GO

[lmdotnet]

关注

[forestyang]

what's name of @indextable?

[wnglish]

@indextable是已经声明一个临时表，这样是对的，但我想把数据库表明当参数传递过去，所有就用了exec，但老调试不过，晕

CREATE procedure s_Message_Page
(
@pagesize int,
@pageindex int
)
as
set nocount on

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_Tables order by id desc
select * from t_Tables O,@indextable t where O.id=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

set nocount off
GO

[mmkk]

http://expert.csdn.net/Expert/topic/2106/2106786.xml?temp=.8503076
http://expert.csdn.net/Expert/topic/2272/2272334.xml?temp=.1327173

[wnglish]

已有朋友帮我调试出来。答案：

CREATE procedure s_Tables_Page
(
@pagesize int,
@pageindex int,
@table varchar(200)
)
as
set nocount on
--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
exec ('declare @indextable table(id int identity(1,1),nid int) insert into '+@indextable+'(nid) select id from '+@table+' order by id desc select * from '+@table+' O,'+@indextable+'t where O.id=t.nid and t.id>'+@PageLowerBound+' and t.id<='+@PageUpperBound+' order by t.id')
set nocount off
GO

[qimini]

UP

[songweiping]

表参数与表名的字符传参数的格式不同，及@table varchar(200)与declare @indextable table(id int identity(1,1),nid int)，正确格式是：

CREATE procedure s_Tables_Page
(
@pagesize int,
@pageindex int,
@table varchar(200)
)
as
set nocount on
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
exec ('insert into @indextable (nid) select id from '+@table+' order by id desc')
exec ('select * from '+@table+' O,@indextable t where O.id=t.nid and t.id>'+@PageLowerBound+' and t.id<='+@PageUpperBound+' order by t.id')

set nocount off

[火电]

CREATE TABLE #table
(
id int identity(1,1),
nid int
)

exec ('insert into #table (nid) select id from '+@table+' order by id desc')

这样试试