CREATE FUNCTION fn_FindReports (@InEmpId nchar(5)) RETURNS @retFindReports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/ AS BEGIN DECLARE @RowsAdded int -- table variable to hold accumulated results DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) -- initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees ed 1. INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @reports SET processed = 2 WHERE processed = 1 END
-- copy to the result of the function the required columns INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO-- Example invocation SELECT * FROM fn_FindReports('11234') GO
create function fn_test() returns @t table (name sysname) as begin insert @t select name from sysobjects insert @t select name from syscolumns return end goselect * from fn_test()drop function fn_test()
create table tb(id int, name varchar(10), pid int, px int) insert into tb values(0 , '栏目分类', 0 , 1) insert into tb values(1 , '动物' , 0 , 1) insert into tb values(2 , '视频' , 0 , 2) insert into tb values(3 , '老虎' , 1 , 1) insert into tb values(4 , '狮子' , 1 , 2) insert into tb values(5 , '搞笑' , 2 , 1) go--查询指定节点及其所有子节点的函数 CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END RETURN END GO --调用函数查询id = 1及其所有子节点 SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID /* id name pid px ----------- ---------- ----------- ----------- 1 动物 0 1 3 老虎 1 1 4 狮子 1 2 (所影响的行数为 3 行) */ drop table tb drop function dbo.f_cid
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 1.
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO-- Example invocation
SELECT *
FROM fn_FindReports('11234')
GO
returns @t table (name sysname)
as
begin
insert @t select name from sysobjects
insert @t select name from syscolumns
return
end
goselect * from fn_test()drop function fn_test()
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1)
insert into tb values(2 , '视频' , 0 , 2)
insert into tb values(3 , '老虎' , 1 , 1)
insert into tb values(4 , '狮子' , 1 , 2)
insert into tb values(5 , '搞笑' , 2 , 1)
go--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO --调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID
/*
id name pid px
----------- ---------- ----------- -----------
1 动物 0 1
3 老虎 1 1
4 狮子 1 2
(所影响的行数为 3 行)
*/
drop table tb
drop function dbo.f_cid
RETURNS @return_variable TABLE < table_type_definition >
中 的@return_variable从某种意义上可以说是个表TABLE 后面的是字段吗?
是用来描述返回表的结构 <> 中写列的描述
如:returns table(name varchar(100))