USE [Learnmanager]
GO
/****** Object: UserDefinedFunction [dbo].[getCount] Script Date: 02/18/2016 14:23:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[getCount]
(
@id int
)
returns @t table ( ccount int, kcount int)
as
begin
declare @ccount int,@kcount int;
with nodes
as
(
select * from k_type where parentid=@id
union all
select child.* from k_type as child,nodes par where child.parentid=par.id
)
select @ccount=COUNT(1) from nodes;
with nodes2
as
(
select * from k_type where id=@id
union all
select child.* from k_type as child,nodes2 par where child.parentid=par.id
)
select @kcount=count(1) from k_info where status=1 and typeid in ( select id from nodes2)
insert into @t(ccount,kcount) select @ccount,@kcount -- values(@ccount,@kcount);
return ;
GO
/****** Object: UserDefinedFunction [dbo].[getCount] Script Date: 02/18/2016 14:23:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[getCount]
(
@id int
)
returns @t table ( ccount int, kcount int)
as
begin
declare @ccount int,@kcount int;
with nodes
as
(
select * from k_type where parentid=@id
union all
select child.* from k_type as child,nodes par where child.parentid=par.id
)
select @ccount=COUNT(1) from nodes;
with nodes2
as
(
select * from k_type where id=@id
union all
select child.* from k_type as child,nodes2 par where child.parentid=par.id
)
select @kcount=count(1) from k_info where status=1 and typeid in ( select id from nodes2)
insert into @t(ccount,kcount) select @ccount,@kcount -- values(@ccount,@kcount);
return ;
end
调用方法:
select * from dbo.getCount(0) b