Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.
 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

3873 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

3873 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

3873 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  
 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