Help with a recursive query - 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

General SQL Server Forums

New to SQL Server Programming

Help with a recursive query

Author

Topic

dhatlas
Starting Member

7 Posts

Posted - 2011-10-12 : 16:41:26

Hi, I am trying to alter a recursive query and I am banging my head against the wall. I have been given this assignment to create user-defined function that you can supply an EmployeeID to and the results will return all employees that report to that Supervisor and any employees that report that employee and on down the line. I am not supposed to return the initial Supervisor's EmployeeID in the result but it keeps coming in as the first result and I can't figure out how to fix it. Any help would be appreciated...here is my code:

CREATE FUNCTION [dbo].[JMfnGetsReports]
(@EmployeeID AS int)
RETURNS @Reports TABLE
(
EmployeeID int NOT NULL,
Supervisor int NOT NULL
)
AS
BEGIN

DECLARE @Employee AS int;

INSERT INTO @Reports
SELECT EmployeeID, Supervisor
FROM Employee
WHERE EmployeeID = @EmployeeID;

SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE Supervisor = @EmployeeID;

WHILE @Employee IS NOT NULL
BEGIN
INSERT INTO @Reports
SELECT *
FROM JMfnGetsReports(@Employee);

SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE EmployeeID > @Employee
AND Supervisor = @EmployeeID;
END

RETURN;

END
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-12 : 17:47:47

Use a recursive CTE - Make this the body of your function:


;with rcte (employeeid, supervisor)
as
(
       select employeeid, supervisor
       from   employee
       where  employeeid = @employeeid
       union all
       select e.employeeid, e.supervisor
       from   rcte r
       join   employee e on e.supervisor = r.employeeid
)
insert @reports (employeeid, supervisor)
select employeeid, supervisor
from rcte
where  employeeid != @employeeid

EDIT:
added the insert to your @reports table

Be One with the Optimizer
TG

dhatlas
Starting Member

7 Posts

Posted - 2011-10-12 : 18:31:35

Thanks for your reply but I don't think this will work for me. I need it to be a user-defined function and I need the CREATE FUNCTION statement in place. With the CTE I am unable to do this. I think there is just a simple change to the above code that I am missing but I can't put my finger on it.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-12 : 19:16:08

As I said use this as the BODY of your user defined function. Just put my code between your BEGIN and RETURN statements (replacing what you have there)

Be One with the Optimizer
TG

dhatlas
Starting Member

7 Posts

Posted - 2011-10-13 : 08:14:07

Thanks, it seems the first time I entered it in I didn't enter it in the correct place and I received an error but when I did enter it between the BEGIN and RETURN and then ran my SELECT query which is (below) the query continued to execute without returning any results. The code I have written does return results but returns the Supervisor I initially start the query with from below and I don't want that included. If anyone has any idea how I can just alter my code just a bit to exclude that initial value from the return I would appreciate it. Thanks.:

SELECT e.EmployeeID, e.LastName, e.FirstName, m.LastName as ReportsTo
FROM Employee e
JOIN JMfnGetsReports(100330) AS r
ON r.EmployeeID = e.EmployeeID
JOIN Employee m

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-13 : 08:42:08

What version of sql server are you using? CTE (common table expression) is only available on 2005 or later.

Be One with the Optimizer
TG

dhatlas
Starting Member

7 Posts

Posted - 2011-10-13 : 08:56:50

SQL 2008

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-13 : 10:55:56

This works for me. Compare to yours to see what went wrong - it it is still not working post working sample data as I have done so we can see the same problem you are seeing.

Be sure not to run this in your actual database as I use the same table name (employee). Note the USE TEMPDB


use tempdb

go
create table employee(employeeid int, supervisor int, lastname varchar(10))
go
insert employee
values (1,null,'emp1')
       ,(2,1,'emp2')
       ,(3,1,'emp3')
       ,(4,1,'emp4')
       ,(5,2,'emp5')
       ,(6,2,'emp6')
       ,(7,5,'emp7')
go

create function getReportsTG(@employeeid int)
returns @reports table 
(employeeid int not null
,supervisor int null)
as 
begin

;with rcte (employeeid, supervisor, hierarchy)
as
(
       select employeeid
              ,supervisor
              ,convert(varchar(max), replace(str(employeeid, 10),' ','0'))
       from   employee
       where  employeeid = @employeeid
       union all
       select e.employeeid
              ,e.supervisor
              ,convert(varchar(max), r.hierarchy + '-' + replace(str(e.employeeid, 10),' ','0'))
       from   rcte r
       join   employee e on e.supervisor = r.employeeid
       where  patindex('%' + replace(str(e.employeeid, 10),' ','0') + '%', r.hierarchy) = 0
)
insert @reports (employeeid, supervisor)
select employeeid, supervisor
from rcte
where  employeeid != @employeeid

return

end
go

SELECT e.EmployeeID,  m.LastName as ReportsTo
FROM   Employee e
JOIN   getReportsTG(1) AS r
       ON r.EmployeeID = e.EmployeeID
JOIN   Employee m
       on m.employeeid = r.supervisor

go

drop table employee
drop function getReportsTG

OUTPUT:
EmployeeID  ReportsTo
----------- ----------
2           emp1
3           emp1
4           emp1
5           emp2
6           emp2
7           emp5

EDIT:
added circular reference check

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-13 : 10:59:24

I just thought of another reason why you might have a problem. Your data could have a circular reference. I will update my last post with code to avoid that - hold on...

Be One with the Optimizer
TG

dhatlas
Starting Member

7 Posts

Posted - 2011-10-13 : 12:59:58

This is what I run and it just continues to query. When I run copy yours exactly using the tempDB it runs just fine:

CREATE FUNCTION [dbo].[JMfnGetsReports]
(@EmployeeID AS int)
RETURNS @Reports TABLE
(
EmployeeID int NOT NULL,
Supervisor int NOT NULL
)
AS
BEGIN

DECLARE @Employee AS int;

INSERT INTO @Reports
SELECT EmployeeID, Supervisor
FROM Employee
WHERE EmployeeID = @EmployeeID;

SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE Supervisor = @EmployeeID;

WHILE @Employee IS NOT NULL
BEGIN
;with rcte (employeeid, supervisor, hierarchy)
as
(
select employeeid
,supervisor
,convert(varchar(max), replace(str(employeeid, 10),' ','0'))
from employee
where employeeid = @employeeid
union all
select e.employeeid
,e.supervisor
,convert(varchar(max), r.hierarchy + '-' + replace(str(e.employeeid, 10),' ','0'))
from rcte r
join employee e on e.supervisor = r.employeeid
where patindex('%' + replace(str(e.employeeid, 10),' ','0') + '%', r.hierarchy) = 0
)
insert @reports (employeeid, supervisor)
select employeeid, supervisor
from rcte
where employeeid != @employeeid
;
END

RETURN;

END
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-13 : 13:08:32

You don't need the WHILE loop. That is why it is called a recursive CTE. It does the recursive part for you.


CREATE FUNCTION [dbo].[JMfnGetsReports]
(@EmployeeID AS int)
RETURNS @Reports TABLE
(
EmployeeID int NOT NULL,
Supervisor int NOT NULL
)
AS
BEGIN
/*
DECLARE @Employee AS int;

INSERT INTO @Reports
SELECT EmployeeID, Supervisor
FROM Employee
WHERE EmployeeID = @EmployeeID;

SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE Supervisor = @EmployeeID;


WHILE @Employee IS NOT NULL
BEGIN
*/
;with rcte (employeeid, supervisor, hierarchy)
as
(
select employeeid
,supervisor
,convert(varchar(max), replace(str(employeeid, 10),' ','0'))
from employee
where employeeid = @employeeid
union all
select e.employeeid
,e.supervisor
,convert(varchar(max), r.hierarchy + '-' + replace(str(e.employeeid, 10),' ','0'))
from rcte r
join employee e on e.supervisor = r.employeeid
where patindex('%' + replace(str(e.employeeid, 10),' ','0') + '%', r.hierarchy) = 0
)
insert @reports (employeeid, supervisor)
select employeeid, supervisor
from rcte
where employeeid != @employeeid
;
--END

RETURN;

END
GO

Be One with the Optimizer
TG

dhatlas
Starting Member

7 Posts

Posted - 2011-10-13 : 14:03:52

Yep, that did the trick...thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-13 : 14:18:09

Great! you're welcome.

Be One with the Optimizer
TG

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-10-13 : 15:27:10

I think recursive is the right solution as TG suggests, but if because of any reason, you still want to use the user-function as you do, you can do it with some changes.

1 - add new parameter in your function, named @callIndicator (ex)
2 - add condition to delete unwanted rows

call function:
outside : select * from yourFunction(EmployeeId, 0)
inside : select * from yourFunction(@EmployeeId, 1)

Below is the change based on your code. With your sample data, it works fine (if I understand your requirement correctly).

ALTER FUNCTION [dbo].[yourFunction]
(@EmployeeID AS int, @callIndicator int)
RETURNS @Reports TABLE
(
EmployeeID int ,
Supervisor int
)
AS
BEGIN

DECLARE @Employee AS int;

INSERT INTO @Reports
SELECT EmployeeID, Supervisor
FROM Employee
WHERE EmployeeID = @EmployeeID;

SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE Supervisor = @EmployeeID;

WHILE @Employee IS NOT NULL
BEGIN

INSERT INTO @Reports (EmployeeID,Supervisor)
SELECT EmployeeID,Supervisor
FROM yourFunction(@Employee, 1);

SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE EmployeeID > @Employee
AND Supervisor = @EmployeeID;

END

if @callIndicator = 0
delete @reports where EmployeeId = @EmployeeId

RETURN;

END

dhatlas
Starting Member

7 Posts

Posted - 2011-10-13 : 17:02:48

That worked too...thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources