| Author |
 Topic  |
|
|
roninblack
Starting Member
4 Posts |
 Posted - 03/07/2013 : 13:00:28
|
Hello, I am having an issue with one of my functions. This is my first time on the boards, and I would greatly appreciate any advice you can give. I will past the code that I've created to create the function and run it, however I'm not sure how to prevent the starting employee from appearing in the results.
======
USE [IST278EagleCorp13-1]
GO
CREATE FUNCTION dbo.BBfnGetsReports
(@EmployeeID AS INT)
RETURNS @REPORTS TABLE
(EmployeeID INT NOT NULL,
Supervisor INT 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 dbo.BBfnGetsReports(@Employee)
SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE EmployeeID>@Employee
AND Supervisor=@EmployeeID
END
RETURN
END
GO
USE [IST278EagleCorp13-1]
GO
SELECT a.EmployeeID, e.LastName, e.FirstName, s.LastName AS ReportsTO
FROM BBfnGetsReports(100330) a JOIN Employee e
ON a.EmployeeID = e.EmployeeID JOIN Employee s
ON a.Supervisor = s.EmployeeID
ORDER BY a.Supervisor
=======
Once again, any guidance would be greatly appreciated.
Thank you! |
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 03/07/2013 : 14:16:38
|
I think you should be able to accomplish that by removing the first INSERT statement:CREATE FUNCTION dbo.BBfnGetsReports
(
@EmployeeID AS INT
)
RETURNS @REPORTS TABLE
(EmployeeID INT NOT NULL, Supervisor INT 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 dbo.BBfnGetsReports(@Employee)
SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE EmployeeID > @Employee
AND Supervisor = @EmployeeID
END
RETURN
END
GOIf you run into performance problems with this code: You should be possible to do this without resorting to a loop if you use a recursive CTE. If you need to do that, reply back. |
Edited by - James K on 03/07/2013 14:16:55 |
 |
|
|
roninblack
Starting Member
4 Posts |
Posted - 03/07/2013 : 14:34:47
|
| Thank you for the quick response. That executes successfully, but unfortunately no results are returned. Is there some type of function that I can insert somewhere to skip over or prevent the first value that is called to not appear? |
|
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 03/07/2013 : 14:56:05
|
Of course what I posted wouldn't work! I had failed to notice that you are calling the function recursively.
You could remove it at the outer query:SELECT a.EmployeeID, e.LastName, e.FirstName, s.LastName AS ReportsTO
FROM BBfnGetsReports(100330) a JOIN Employee e
ON a.EmployeeID = e.EmployeeID JOIN Employee s
ON a.Supervisor = s.EmployeeID
WHERE a.EmployeeID <> 100330
ORDER BY a.Supervisor |
|
|
|
roninblack
Starting Member
4 Posts |
Posted - 03/07/2013 : 15:08:34
|
Wonderful! That works completely. I am wondering however, just to pick you brain on the matter. Would it be possible to incorporate that into the initial part, meaning. Whatever EmployeeID I enter in, it would list everything except for the columns of the EmployeeID that was entered.
And thank you, for your continued support. I am rather new at this, and really appreciate the guidance. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 03/07/2013 : 17:09:39
|
You can exclude it if you add another variable like shown below. I also want to suggest looking into a recursive CTE - which would be more efficient; if you are interested, I (someone else on the forum) can post more details:CREATE FUNCTION dbo.BBfnGetsReports
(
@EmployeeID AS INT,
@Level AS INT
)
RETURNS @REPORTS TABLE
(EmployeeID INT NOT NULL, Supervisor INT NULL)
AS
BEGIN
DECLARE @Employee AS INT
IF (@Level > 0 )
BEGIN
INSERT INTO @Reports
SELECT EmployeeID,
Supervisor
FROM Employee
WHERE EmployeeID = @EmployeeID
END
SET @Level = @Level + 1;
SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE Supervisor = @EmployeeID
WHILE @Employee IS NOT NULL
BEGIN
INSERT INTO @Reports
SELECT *
FROM dbo.BBfnGetsReports(@Employee, @Level)
SELECT @Employee = MIN(EmployeeID)
FROM Employee
WHERE EmployeeID > @Employee
AND Supervisor = @EmployeeID
END
RETURN
END
GO
SELECT a.EmployeeID, e.LastName, e.FirstName, s.LastName AS ReportsTO
FROM BBfnGetsReports(100330,0) a JOIN Employee e
ON a.EmployeeID = e.EmployeeID JOIN Employee s
ON a.Supervisor = s.EmployeeID
ORDER BY a.Supervisor |
|
|
|
roninblack
Starting Member
4 Posts |
Posted - 03/08/2013 : 15:31:11
|
| Thank you very much for breaking that down for me. I greatly appreciate it. |
|
|
| |
Topic |
|
Help with user defined function.
