?
Solved

Recursive function not working properly

Posted on 2006-05-11
6
Medium Priority
?
277 Views
Last Modified: 2006-11-18
I submitted a question last month regarding, as I originally stated it, a recursive JOIN. The solution was a function that returned the values I needed. It appeared to work properly, and as this is new development, the requirements changed.

Originally, I described the situation as having a Positions table and an Employees table. The Positions table have Position Number, Employee ID and ReportsTo Employee ID. There is also an indicator to show whether the position is vacant or not.

The original question I had was how to return the immediate supervisor AND that supervisor's supervisor, taking into account that I want to ignore a supervisor if his position is vacant (now that I think about it, it was kind of dumb to set it up that way originally to begin with).

It has now been chaged so that ReportsTo Employee ID is ReportsTo Position, which makes a lot more sense. However, I still have the same need to traverse UP the chain and return two supervisors, again ignoring vacant positions. I made some modifications to the original function, but get an error if I am unable to successfully retrieve two values. In other words, if a Director reports to a VP who reports to a CEO, who is at the top of the hierarchy (in other words, does not have a ReportsTo Position), and all three positions are not vacant, it works if I run the procedure for the director, but erros if I run it with the VP or CEO. If that is the case, I just want to return NULL for the values. Following is my modified function and the procedure that calls it. Following that is some sample code to build a couple of test tables and another description of what I am trying to do.

(The error I am getting is: -2147217900   [Microsoft][ODBC SQL Server Driver][SQL Server]Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)--when two names are not being returned)

==== current function ====
CREATE function fn_supervisor (@EmpID int)
returns int
as
begin
    declare @ReportToEmpID int
    declare @ReportToPosition nvarchar(10)

    select @ReportToPosition = ReportsTo from tblPositions where TelEmpID = @EmpID
    select @ReportToEmpID = TelEmpID from tblPositions where PositionNbr = @ReportToPosition

    if nullif(@ReportToEmpID, '') is not null
        return @ReportToEmpID
    else
        if exists (select count(*) from tblPositions where Vacant = 1 and PositionNbr = @ReportToPosition)
            begin
                return dbo.fn_supervisor(@ReportToEmpID)
            end
        return @ReportToEmpID
   end

==== current procedure ====
CREATE PROCEDURE hr_employee_get_supervisors
      @TelEmpID      int,
      @Sup1ID      int             OUTPUT,
      @Sup1LName      nvarchar(20)      OUTPUT,
      @Sup1FName      nvarchar(20)       OUTPUT,
      @Sup2ID      int             OUTPUT,
      @Sup2LName      nvarchar(20)      OUTPUT,
      @Sup2FName      nvarchar(20)       OUTPUT
AS
      SET NOCOUNT ON


      SELECT      @Sup1ID = ISNULL(TelEmpID, 0),
                  @Sup1LName = ISNULL(Lname, ''),
                  @Sup1FName = ISNULL(Fname, '')
      FROM            tblEmployees
      WHERE       TelEmpID = dbo.fn_supervisor (@TelEmpID)

      SELECT      @Sup2ID = ISNULL(TelEmpID, 0),
                  @Sup2LName = ISNULL(Lname, ''),
                  @Sup2FName = ISNULL(Fname, '')
      FROM            tblEmployees
      WHERE       TelEmpID = dbo.fn_supervisor (dbo.fn_supervisor (@TelEmpID))

GO

==== test tables ====
CREATE TABLE [dbo].[tblEmployees] (
     [TelEmpID] [int] IDENTITY (1, 1) NOT NULL ,
     [Lname] [nvarchar] (50) NULL ,
     [Fname] [nvarchar] (50) NULL ,
     [MI] [nvarchar] (2) NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[tblPositions] (
     [PositionNbr] [nvarchar] (10) NOT NULL ,
     [Classification] [nvarchar] (50) NOT NULL ,
     [TelEmpID] [int] NULL,
     [Vacant] [bit] NOT NULL ,
     [ReportTo] [nvarchar] (10) NULL
) ON [PRIMARY]

==== add data ====
tblEmployees
---------------
INSERT INTO tblEmployees (TelEmpID, Lname, Fname, MI) VALUES ('Walker', 'George', 'A')  -- TelEmpID = 1
INSERT INTO tblEmployees (TelEmpID, Lname, Fname, MI) VALUES ('Doe', 'John', 'J')  -- TelEmpID = 2
INSERT INTO tblEmployees (TelEmpID, Lname, Fname, MI) VALUES ('Doe', 'Jane', 'L')  -- TelEmpID = 3
INSERT INTO tblEmployees (TelEmpID, Lname, Fname, MI) VALUES ('Smith', 'John', NULL)  -- TelEmpID = 4
INSERT INTO tblEmployees (TelEmpID, Lname, Fname, MI) VALUES ('Jones', 'Robert', 'S')  -- TelEmpID = 5
INSERT INTO tblEmployees (TelEmpID, Lname, Fname, MI) VALUES ('Public', 'John', 'Q')  -- TelEmpID = 6
INSERT INTO tblEmployees (TelEmpID, Lname, Fname, MI) VALUES ('Wilson', 'Bob', 'W')  -- TelEmpID = 7

tblPositions
-------------
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P001', 'VP', 1, 0, NULL)
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P002', 'Janitor', 5, 0, 'P008')
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P003', 'Programmer', 6, 0, 'P005')
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P004', 'Key Punch Operator', 7, 0, 'P005')
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P005', 'Manager', 4, 0, 'P006')
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P006', 'Supervisor', NULL, 1, 'P007')
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P007', 'Director', 2, 0, 'P001')
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P008', 'Manager', NULL, 1, 'P009')
INSERT INTO tblPositions (PositionNbr, Classification, TelEmpID, Vacant, ReportTo) VALUES
            ('P009', 'Director', NULL, 1, 'P001')

=================================================================
This should be the hierarchy that the function would return, given that, if the position identified by the
field ReportTo is vacant, it is ignored, it's ReportTo position is then checked, etc.

Position    Classification         Employee                Reports To 1             Reports To 2
========    =============          ================        =================        =================
P001        VP                     Walker, George A
P002        Janitor                Jones, Robert S         Walker, George A
P003        Programmer             Public, John Q          Smith, John              Doe, John J
P004        Key Punch Operator     Wilson, Bob W           Smith, John              Doe, John J
P005        Manager                Smith, John             Doe, John J              Walker, George A  
P006        Supervisor                                     Doe, John J              Walker, George A
P007        Director               Doe, John J             Walker, George A        
P008        Manager                                        Doe, John J              Walker, George A
P009        Director                                       Walker, George A

If I execute the procedure and pass it the TelEmpID of the VP, it would return all NULLs (actually an empty string
since I'm using NULLIF(). If I pass the TelEmpID of the Director, it would return values for the first supervisor but
NULLs for the second, while the TelEmpID of the Programmer would return values in all the variables.

Notice that the name for Supervisor and the second Director is not on the report because the positions are vacant.
I want at least two upper-level managers, excluding any vacant positions, for each employee, if they exist.
Since the VP is the top level position, there would be no supervisors over him and there is only one supervisor
over the Director. Notice also in this example that, although the Janitor (P002) reports to P008, and P008
reports to P009, who then reports to P001, because P008 and P009 are vacant, the janitor reports directly to the VP.
0
Comment
Question by:dbbishop
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16660848
try adding a check for null @EmpID to the beginning of your function

0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16660967
If @EmpID = NULL should be used intead of the NULLIF you are using.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16661104
dbbishop,
> (The error I am getting is: -2147217900   [Microsoft][ODBC SQL Server
> Driver][SQL Server]Maximum stored procedure, function, trigger, or view
> nesting level exceeded (limit 32)--when two names are not being returned)


The default nesting level of sql server is 32..
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 15

Author Comment

by:dbbishop
ID: 16662719
Sorry all, no help!

BriCrowe: Null can be a valid value to be passed since, if the position is vacant, the employee ID for the position would be null. I want to keep traversing up the hierarchy until I get an employee ID that is not null or get to the top of the hierarchy.

Atlanta_Mike: I did change that. It got inadvertantly left in when I was trying to modify it using the ReportTo position number instead of the employee ID. Taking it out made no difference.

aneeshattingal: I am aware of that. What I need the function to be able to do is signal back to calling procedure when it has reached the TOP of the hierarchy and have the procedure stop calling the function recursively. However, I am not sure how to do this since the procedure is passing the return value of the function directly to the function to get the second-level supervisor. I get a headache as it is trying to work with recursive functions.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16680131
>What I need the function to be able to do is signal back to calling procedure when it has reached the TOP of the
>hierarchy and have the procedure stop calling the function recursively

You can check the value of @@nestLevel inside the function and if it reaches 31, then returmn some -ve values

IF @@NESTLEVEL = 31
  RETURN -1

0
 
LVL 15

Author Comment

by:dbbishop
ID: 16683722
aneeshattingal:
I was able to get that to work. However, I had to break up the code in the procedure to do away with the nested calls to the function (dbo.fn_supervisor (dbo.fn_supervisor (@TelEmpID))) in order to make it work. Since the function returns the supervisor's ID, I return inot a variable (SELECT @SuperID = dbo.fn_supervisor (@TelEmpID)) and then use the variable in the subsequent query. If it returns -1 the first time, I set all return variables to empty valuse and return. Otherwise, I collect the first set and then call the function again, this time using @SuperID as the parameter. That lets me check interim values, which I could not do with the function calls nested.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question