Problem in stored procedure - SQL Server Forums

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)

Problem in stored procedure

Author

Topic

zafarthesultan
Starting Member

16 Posts

Posted - 2009-07-13 : 03:00:56

This is my stored procedure

ALTER PROCEDURE TeamStructure
(
@rootid int
)
AS
WITH MyTeam
AS
(
SELECT *, [Name] AS ParentName, 0 AS TeamLevel FROM tbl_team
WHERE ParentID =@rootid UNION ALL
SELECT Fam.*,MyTeam.Name AS ParentName, TeamLevel + 1 FROM tbl_team AS Fam
INNER JOIN MyTeam ON Fam.ParentID = MyTeam.ID)
SELECT * FROM MyTeam

It will return the complete hierarchical records.
Level 0 will have root member
Level 1 will have 2 members
Level 2 willhave 4 members
Level 3 will have 8 members
or if level =n
members= 2^n
I need to perform the following operation:
for any level if members are less than 2^n the iteration should stop and return the number of members on that level and the number of level
For example if the number of members on level 4 are 12
The procedure should stop and return
level :4
Members :12

Can anyone modify this procedure and help me please?
TIA

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-13 : 03:12:34

Hi, im not good at hierachy...but i hope the following links can help you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128776

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-13 : 04:05:16

[code]ALTER PROCEDURE [dbo].[CATCHLEVEL] (@lvl int)
AS
DECLARE @decision INT
BEGIN
if @lvl < 3 SET @decision = 1
else SET @decision = 2;

WITH EmpCTE(empid, empname, mgrid, depth, sortcol)
AS
(
-- anchor member
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM employees
WHERE empid = 1
UNION ALL
-- recursive member
SELECT E.empid, E.empname, E.mgrid, M.depth+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
-- outer query
SELECT
REPLICATE('| ', depth) + '(' + (CAST(empid AS VARCHAR(10))) + ') ' + empname AS empname
FROM EmpCTE
where (depth = @lvl-1 and @decision <> 1) or (@decision = 1)
END[/code]
will not display all member after depth > 2, only show same level
ps. wait khtan or peso they all confirm

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources