当前位置 > 首页 > Sql

T-SQL函数遍历BOM表.

2010-11-24 10:39:00来源:Sql

/*

调用方法如下:
select * from f_BOM('a.1')
意思是找出a.1下的所有儿子及孙子

*/


create table BOM(

pType varchar(10),
subTtype varchar(20),
amount int
) on [primary]
?


表结构如下:
ptype subptype amount
a a.1 20
a a.2 15
a a.3 10
a.1 a.1.1 20
a.1 a.1.2 15
a.1 a.1.3 30
a.2 a.2.1 10
a.2 a.2.2 20
a.1.1 a.1.1.1 45
a.1.1 a.1.1.2 15
a.2.1 a.2.1.1 20
a.2.2 a.2.2.1 13

函数如下:
CREATE FUNCTION f_BOM (@ProductID varchar(5))
RETURNS @retPLExpand TABLE (Ptype varchar(50),SubPtype varchar(50),
Amount smallint NOT NULL
)
-- 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
declare @PLExpand Table (Ptype varchar(50),SubPtype varchar(50),
Amount smallint NOT NULL,
processed tinyint default 0)

INSERT @PLExpand
SELECT Ptype,SubPtype, Amount, 0
FROM BOM?
WHERE Ptype = @ProductID
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 @PLExpand
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1.
INSERT @PLExpand
SELECT Ptype, SubPtype, Amount, 0
FROM bom?
WHERE ltrim(Ptype) in (select ltrim(subptype) from @PLExpand where processed = 1)
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @PLExpand
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retPLExpand
SELECT Ptype, SubPtype, Amount
FROM @PLExpand
RETURN
END