Loading ...
Sorry, an error occurred while loading the content.

Re: [PBML] Building and navigating a tree

Expand Messages
  • Bob Alexander
    ... Using MySQL on both XP and Linux. ... Very interesting Jenda. Thank you very much. Bob
    Message 1 of 7 , Jul 27, 2005
    • 0 Attachment
      Jenda Krynicky wrote:
      > CREATE PROCEDURE CountSubordinates( @MgrId Int, @Count Int OUTPUT)
      > as
      > BEGIN
      > CREATE TABLE #subs (ID Int, Level SmallInt);
      > Declare @Level SmallInt;
      > SET @Level = 1
      > INSERT INTO #subs (ID, Level)
      > VALUES (@MsgId, 0)
      > WHILE (@@ROWCOUNT > 0 and @Level < 20) BEGIN
      > INTO #subs (ID, Level)
      > SELECT EmployeeSerialNumber, @Level
      > FROM #subs
      > JOIN TheTable ON TheTable.ManagerSerialNumber = #subs.ID
      > WHERE #subs.Level = @Level - 1
      >
      > SET @Level = @Level + 1
      > END
      > IF (@Level = 20) BEGIN
      > raiserror('20 levels of management? Are you sure there is no
      > loop?',13,1);
      > return;
      > END
      >
      > SELECT @Count = count(*)-1
      > FROM #subs
      >
      > DROP TABLE #subs
      > END
      >
      > (MS SQL Server syntax, not tested)
      >

      Using MySQL on both XP and Linux.

      > Jenda

      Very interesting Jenda.

      Thank you very much.

      Bob
    Your message has been successfully submitted and would be delivered to recipients shortly.