select count(ThreadID) from @TableName where fatherid=aaa 有问题的 一般情况下表名是不能用变两代替的啊,先用一个字符串组织要实现的语句 然后用 exec sp_executesql存储过程实现.
把插入和选择语句都赋值跟一个字符串变量 然后用EXEC执行
to guxiaobo1982(风沙):我刚才看FAQ知道了这个解决办法,可我对存储过程几乎是一窍不通,我是做C#的,存储过程是前10来分钟才看了看资料,不懂啊,我看到sp_executesql似乎还得定义输入输出的参数,就包括输出的时候有些什么都要定义吗?
CREATE procedure ReadThreadData (@TableName VarChar(10), @OraderKey VarChar(20), @pagesize int, @pageindex int, @docount bit) as set nocount on if(@docount=1)exec('select count(ThreadID) from '+@TableName+' where fatherid=aaa') else begincreate table #indextable (id int identity(1,1),nid int) declare @PageLowerBound int declare @PageUpperBound int set @PageLowerBound=(@pageindex-1)*@pagesize set @PageUpperBound=@PageLowerBound+@pagesize set rowcount @PageUpperBoundexec('insert into #indextable(nid) select ThreadID from '+@TableName+' where fatherid=aaa order by '+@OraderKey+' desc')declare @a varchar(8000)set @a='select O.* from '+@TableName+' O,#indextable t where O.ThreadID=t.nid and t.id>'+cast(@PageLowerBound as varchar(10))+' and t.id<='+cast(@PageUpperBound as varchar(10))+' order by t.id' exec(@a)end set nocount off GO
alter proc ReadThreadData (@TableName VarChar(10), @OraderKey VarChar(20), @pagesize int, @pageindex int, @docount bit) as set nocount on if(@docount=1)exec('select count(ThreadID) from '+@TableName+' where fatherid=aaa') else begincreate table #indextable (id int identity(1,1),nid int) declare @PageLowerBound int declare @PageUpperBound int set @PageLowerBound=(@pageindex-1)*@pagesize set @PageUpperBound=@PageLowerBound+@pagesize set rowcount @PageUpperBoundexec('insert into #indextable(nid) select ThreadID from '+@TableName+' where fatherid=aaa order by '+@OraderKey+' desc')declare @a varchar(8000)set @a='select O.* from '+@TableName+' O,#indextable t where O.ThreadID=t.nid and t.id>'+cast(@PageLowerBound as varchar(10))+' and t.id<='+cast(@PageUpperBound as varchar(10))+' order by t.id' exec(@a)end set nocount off GO
有问题的 一般情况下表名是不能用变两代替的啊,先用一个字符串组织要实现的语句 然后用
exec sp_executesql存储过程实现.
然后用EXEC执行
(@TableName VarChar(10),
@OraderKey VarChar(20),
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)exec('select count(ThreadID) from '+@TableName+' where fatherid=aaa')
else
begincreate table #indextable (id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBoundexec('insert into #indextable(nid) select ThreadID from '+@TableName+' where fatherid=aaa order by '+@OraderKey+' desc')declare @a varchar(8000)set @a='select O.* from '+@TableName+' O,#indextable t where O.ThreadID=t.nid and t.id>'+cast(@PageLowerBound as varchar(10))+' and t.id<='+cast(@PageUpperBound as varchar(10))+' order by t.id'
exec(@a)end
set nocount off
GO
(@TableName VarChar(10),
@OraderKey VarChar(20),
@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)exec('select count(ThreadID) from '+@TableName+' where fatherid=aaa')
else
begincreate table #indextable (id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBoundexec('insert into #indextable(nid) select ThreadID from '+@TableName+' where fatherid=aaa order by '+@OraderKey+' desc')declare @a varchar(8000)set @a='select O.* from '+@TableName+' O,#indextable t where O.ThreadID=t.nid and t.id>'+cast(@PageLowerBound as varchar(10))+' and t.id<='+cast(@PageUpperBound as varchar(10))+' order by t.id'
exec(@a)end
set nocount off
GO