存储过程获取子分类id

<script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trades]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trades]
GO

CREATE TABLE [dbo].[trades] (
 [tradeid] [bigint] NOT NULL ,
 [parentid] [bigint] NOT NULL ,
 [tradename] [nchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
 [sortid] [int] NULL ,
 [prefix] [nchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

 

 

CREATE  PROCEDURE getchildid
@aid bigint,
@childid varchar(200) output
 AS
declare @curid bigint
declare @tid varchar(200)
set @childid=@aid

declare cur cursor local for select tradeid   from trades where parentid=@aid order by sortid
open cur
fetch next from cur into @curid

while(@@fetch_status=0)
begin
 
 exec getchildid @curid, @tid output
 set @childid=@childid+','+@tid
 fetch next from cur into @curid
end
close cur
deallocate cur
GO