SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help with user defined function.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

roninblack
Starting Member

4 Posts

Posted - 03/07/2013 :  13:00:28  Show Profile  Reply with Quote
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

3323 Posts

Posted - 03/07/2013 :  14:16:38  Show Profile  Reply with Quote
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.

Edited by - James K on 03/07/2013 14:16:55
Go to Top of Page

roninblack
Starting Member

4 Posts

Posted - 03/07/2013 :  14:34:47  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/07/2013 :  14:56:05  Show Profile  Reply with Quote
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
Go to Top of Page

roninblack
Starting Member

4 Posts

Posted - 03/07/2013 :  15:08:34  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/07/2013 :  17:09:39  Show Profile  Reply with Quote
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
Go to Top of Page

roninblack
Starting Member

4 Posts

Posted - 03/08/2013 :  15:31:11  Show Profile  Reply with Quote
Thank you very much for breaking that down for me. I greatly appreciate it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000