Getting data from recursive procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting data from recursive procedure

Author

Topic

lukevid
Starting Member

1 Post

Posted - 2007-05-07 : 11:48:50

Hello,
I need a procedure which builds a structure (starting from given node) tree and counts all salaries

Table is like this one:

EmpID int
EmpName varchar
MgrId varchar
salary real

I've found following procedure:

CREATE PROC dbo.ShowHierarchy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)

SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName

SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)

WHILE @EmpID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @EmpID
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)
END

It works quite well, however I would like to also receive information about all salaries (sum) for given root. I've tried to give OUTPUT parameter:

CREATE PROC dbo.ShowHierarchy
(
@Root int, @Salary real output
...
@salary = @salary + select salary from dbo.Emp where EmpID = @EmpID
....

I call this procedure from:
declare @sal real
set @sal = 0
dbo.ShowHierarchy 10, @salary=@sal output
print @sal

And it doesn't work. It is counting, but finally it returns
value from first node. Where is my mistake?

Thanks in advance

Luke

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-07 : 11:52:22

Your mistake is in using recursive SQL. Recursion is supported in SQL, but it is not recommended. Use a temporary table instead, and you will find the coding easier and the execution times faster. Here is an example:
http://sqlblindman.googlepages.com/returningchildrecords

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources