sql server 2005 UDF 执行问题
USE AdventureWorksgoCREATE FUNCTION dbo.fnGetReports (@EmployeeID AS int) RETURNS @Reports TABLE ( EmployeeID int not NULL, ManagerID int not NULL )ASBEGIN/*Snce we'll need to this function recursively - that is once for each reporting ** emloyee (to make sure that they don't have reports of their own), we need a holding ** variable to keep track of which employee we're currently working on.*/ DECLARE @Employee AS int/*This inserts the current employee into our working table.The significance hereis**that we need the first record as something of s primer due to the recursive nature of the function - this is how we get it.*/INSERT INTO @Reports SELECT EmployeeID,ManagerID FROM HumanResources.Employee WHERE EmployeeID = @EmployeeID/*Now we also need a primer for the recursive calls we're getting ready to start making to this function .This would probably be better done with a cursor ,but we haven't gotten to that chapter yet,so...*/SELECT @Employee = MIN(EmployeeID)FROM HumanResources.EmployeeWHERE ManagerID = @EmployeeID/* This next part would probably be better done with a curson but we haven't gottento that chapter yet ,so we'll fake it .Notice the recursive call to our function!*/WHILE @Employee IS NOT NULL BEGIN INSERT INTO @Reports SELECT * FROM fnGetReports(@Employee) SELECT @EmployeeID = MIN(EmployeeID) FROM HumanResources.Employee WHERE EmployeeID > @Employee AND ManagerID = @EmployeeID END RETURN ENDGO
SELECT * FROM fnGetReports(12)
