Help with user defined function.

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 2012 Forums

Transact-SQL (2012)

Help with user defined function.

Author

Topic

roninblack
Starting Member

4 Posts

Posted - 2013-03-07 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-07 : 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
GO

If 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.

roninblack
Starting Member

4 Posts

Posted - 2013-03-07 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-07 : 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 - 2013-03-07 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-07 : 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 - 2013-03-08 : 15:31:11

Thank you very much for breaking that down for me. I greatly appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources