ALTER PROCEDURE dbo.GetFamilyParts
(
@node int
)
AS
SET NOCOUNT ON
select a.* from Part a,dbo.GetPartsFamily(@node) b where a.ID=b.Part
returnALTER PROCEDURE dbo.GetPartsFamily
(
@id int
)
AS
SET NOCOUNT ON
declare @t table (id int,Level int)
declare @Level int
set @Level=0
insert @t values(@id,@Level)
while exists (select 1
from @t t,PartsClass a
where a.Parent=t.id
and t.Level=@Level
)
begin
insert @t select a.id,@Level+1
from @t t,PartsClass a
where a.Parent=t.id
and t.Level=@Level
set @Level = @Level+1
end
Select a.* from PartsClass a,@t t where a.ID=t.id
return
请问我这样写对吗?
create table #t (collist ....)
insert #t exec dbo.GetPartsFamily(@node)
select * .....
不对,执行存储过程用 exec 存储过程名
这个存储过程要放入另外一个表里面,
http://blog.csdn.net/fredrickhu/archive/2009/09/23/4584118.aspx请看
from OPENROWSET('SQLOLEDB','SERVER=.;UID=sa;PWD=','exec servername.dbo.GetPartsFamily 2') AS
where a.ID=b.Part
OPENROWSET('SQLOLEDB','SERVER=.;UID=sa;PWD=','exec servername.dbo.GetPartsFamily 2') AS
where a.ID=b.Part