急，如何在用户函数中用变量作为查询语句的表名

[redfoxhuang]

CREATE function f_getchildidsort_multi(@Code varchar(100),@Dbname varchar(100))
returns @re table(id int,Class_Code varchar(100),Class_PareCode varchar(100))
as
begin
declare @string varchar(1000)
set @string='insert into @re select Class_id,Class_Code,Class_PareCode
from '+@Dbname+'_class where Class_Code=@Code
insert into @re select Class_id,Class_Code,Class_PareCode
from '+@Dbname+'_class where Class_PareCode=@Code'
exec(@string)
~~~~~~~~~~~~~~~这里报错
while @@rowcount>0
....
end
========================
函数里面怎么用啊？报错说函数里面错误的运用了execute
有没有解决的方法？

[pengdali]

只可以在过程里用exec

[redfoxhuang]

那如何用传递的参数作为select语句的表名啊？在函数中

[aierong]

exec不可以在自定义函数里面用

你可以把函数该为存储过程

[aierong]

CREATE proc proc_ai
@Code varchar(100),@Dbname varchar(100)
as
create table #re(id int,Class_Code varchar(100),Class_PareCode varchar(100))
as
begin
declare @string varchar(8000)
select @string='insert into #re select Class_id,Class_Code,Class_PareCode
from '+@Dbname+'_class where Class_Code=@Code'+ ' insert into #re select Class_id,Class_Code,Class_PareCode
from '+@Dbname+'_class where Class_PareCode=@Code'
exec(@string)
while @@rowcount>0
....

[redfoxhuang]

我的函数的作用就是对树形数据的遍历
还不知道用存储过程的方法
只能先改这个

[redfoxhuang]

谢谢！我试试看

[redfoxhuang]

奇怪了，我用
========================
CREATE proc proc_ai
@Code varchar(100),@Dbname varchar(100)
as
create table #re(id int,Class_Code varchar(100),Class_PareCode varchar(100))
begin
insert into #re select Class_id,Class_Code,Class_PareCode
from class where Class_Code=@Code
insert into #re select Class_id,Class_Code,Class_PareCode
from class where Class_PareCode=@Code
while @@rowcount>0
insert into #re select a.Class_id,a.Class_Code,a.Class_PareCode
from class a inner join #re b on a.Class_PareCode=b.Class_Code
where a.Class_Code not in(select Class_Code from #re)

select * from #re

end
GO
exec proc_ai '-1','aa'
===================================
可以得出结果
但是用
============================================

CREATE proc proc_ai
@Code varchar(100),@Dbname varchar(100)
as
create table #re(id int,Class_Code varchar(100),Class_PareCode varchar(100))
begin
declare @string varchar(8000),@string1 varchar(8000)
select @string='insert into #re select Class_id,Class_Code,Class_PareCode
from class where Class_Code='+@Code+'
insert into #re select Class_id,Class_Code,Class_PareCode
from class where Class_PareCode='+@Code
exec(@string)
while @@rowcount>0
select @string1='insert into #re select a.Class_id,a.Class_Code,a.Class_PareCode
from class a inner join #re b on a.Class_PareCode=b.Class_Code
where a.Class_Code not in(select Class_Code from #re)'
exec(@string1)

end


GO
就会出错，存储过程是建立了，但是一调用，就是报错
比如exec proc_ai 'test','aa'
就说test字段不存在，用exec proc_ai '-1','aa'就报错说将字符窜转化成int类型的时候出错
class表里面就三个字段
Class_id,Class_Code,Class_PareCode（父节点）