T-SQL Experts - Can this SP be recoded to work as a UDF
-
I wrote the following SP that enables me to retrieve everyone who reports to a particular employee. For example I can get a table of all the employees working for the head of HR.
The problem with SP's is that they can't be invoked in a View, nor can they be specified in Excel using Excel's native Database Query functionality. Views only recognize Tables, Views and UDF's. Excel only recognizes Tables and Views.
I suppose I could write VBScript code in Excel that would invoke the SP, or T-SQL that would output an Excel (CSV?) formatted file, but I'd really like to see if there's a way to recode this so it will work in a UDF that returns a Table.
Here's the SP:
- CREATE PROCEDURE [dbo].[GetDirects] (@empid char(6))
AS
BEGIN
DECLARE @rows int
select distinct employeeid as emp into #hrtemp from Directory where higmanagerempid = @empid
set @rows = @@rowcount
while @rows > 0
BEGIN
insert into #hrtemp select employeeid as emp from Directory where
- higmanagerempid in (select emp from #hrtemp) AND
- employeeid NOT IN (select emp from #hrtemp)
- set @rows = @@rowcount
END
SELECT employeeid, sn, givenname, mail from #hrtemp join Directory on emp=employeeid where len(mail)>0
END
GO
It's not a complex one, but I can't get my head around how to recurse the heirarchy within the limitations of UDF code.
Any help is greatly appreciated.
- CREATE PROCEDURE [dbo].[GetDirects] (@empid char(6))
-
Try this, it's not all that much differnet from what you already have:
Create FUNCTION
dbo.udf_GetEMpsBYManager
(
@empid
char(6)
)
RETURNS
@table_variable TABLE (employeeid char(6), sn varchar(15), givenname varchar(25), mail varchar(50) )
AS
BEGIN
Declare @temp Table (employeeid char(6))
Declare @rows Int
Insert Into @temp
Select Distinct
employeeid
From
directory
Where
higmanagerempid = @empid
While @rows > 0
Begin
Insert Into @temp
Select Distinct
D.employeeid
From
directory D
Left Join
@temp T
On
D.higmanagerempid = T.employeeid
Where
T.employeeid
Is Null
Set @rows = @@rowcount
End
Insert Into @table_variable
Select
D.employeeid,
D.sn,
D.givenname,
D.mail
From
@temp T
Join
Directory D
on
R.empployeeid = D.employeeid
Where
len(D.mail)>0
RETURN
END
I always use joins when I can because I find htem easier to read than subselects. There probably is a better way to get through the heirarchy but this should work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
This will give you only the employees where theit higmanagerempid isn't in the table variable.
Select Distinct
D.employeeid
From
directory D Left Join
@temp T On
D.higmanagerempid = T.employeeid
Where
T.employeeid Is Null
Think it should be
Select Distinct
E.employeeid
FROM
(
Select Distinct
D.employeeid
From
directory D Left Join
@temp T On
D.higmanagerempid = T.employeeid
) As E LEFT JOIN
@temp T2
ON
D.employeeid = T2.employeeid
WHERE
T2.employeeid IS NULL
-
Recursive function example;Trick is using simplified create function,
then use alter function to add the recursive call.
http://www.sqlservercentral.com/scripts/contributions/890.asp
Regards,Coach James
-
I took Jack's code, modified it with the changes suggested by Antares and I get this:
Create FUNCTION dbo.udf_GetEMpsBYManager
(
@empid char(6)
)
RETURNS @table_variable TABLE (employeeid char(6), sn varchar(15), givenname varchar(25), mail varchar(50) )
AS
BEGIN
Declare @temp Table (employeeid char(6))
Declare @rows Int
Insert Into @temp
Select Distinct employeeid
From directory
Where higmanagerempid = @empid
While @rows > 0
Begin
Insert Into @temp
Select Distinct E.employeeid
From
(
Select Distinct D.employeeid
From directory D Left Join @temp T
On D.higmanagerempid = T.employeeid
)
As E LEFT JOIN @temp T2
On E.employeeid = T2.employeeid
Where T2.employeeid Is NULL
Set @rows = @@rowcount
End
Insert Into @table_variable
Select D.employeeid, D.sn, D.givenname, D.mail
From @temp T Join Directory D
On T.employeeid = D.employeeid
Where len(D.mail)>0
RETURN
END
Executing this code only returns the first level of direct reports to the employee_id specified in the UDF parameter (12 employees). My original SP returns 432 employees all the way to the bottom of the chain.
Did I mis-code something? I thought I applied Antares' changes correctly.
nb. Coach James, I can see your point theoretically but I have no idea how to apply it to my case.
-
I believe this needs "DECLARE <name> INSENSITIVE CURSOR FOR";otherwise its not in a loop...Insert Into @temp
Select Distinct employeeid
From directory
Where higmanagerempid = @empid
My suggestion was: recalling a UDF with @empid (for each level) until none found. You can still share temp tables and results, so all iterations provide a complete tree search.
The UDF is passed @empid the first time and retrieves next level; it calls itself on each new @empid found until none found (thus recursive heirarchy) and until all managerial relations are found under first @empid.
The example of UDF iteration or recursion provided previously was a simple one showing UDF recursion being used to break-out a string.
Later this month I'll be building UDF recursive functions for about 3 or 4 different tables where UDF iteration will be used to retrieve data tree relations (based on same table hierarchy) & will post when complete.
Regards,Coach James
-
Here's the final, working solution:
CREATE FUNCTION dbo.udf_GetEMpsBYManager
(
@empid char(6)
)
RETURNS @table_variable TABLE (employeeid char(6), sn varchar(50), givenname varchar(50), mail varchar(200) )
AS
BEGIN
Declare @temp Table (employeeid char(6))
Declare @rows Int
- Insert Into @temp
Select Distinct employeeid
From directory
Where higmanagerempid = @empid
Set @rows = @@rowcount <== Had to add this so While works
- the first time through
- While @rows > 0
Begin
Insert Into @temp
Select Distinct E.employeeid
From
(
Select Distinct D.employeeid <== From here to
From directory D join @temp T
On D.higmanagerempid = T.employeeid
Where D.employeeid not in
(
Select employeeid from @temp
 
)
As E <== here
- This part was changed to select only the employeeid's from
- the Directory where the Directory Manager ID = the Temp
- file's employeeid and the Directory EmployeeID was not already
- in the Temp File
Set @rows = @@rowcount
End
Insert Into @table_variable
Select D.employeeid, D.sn, D.givenname, D.mail
From @temp T Join Directory D
On T.employeeid = D.employeeid
Where len(D.mail)>0
RETURN
END
The result is exactly the same 432 rows that my SP retrieves.
Coach, I could probably redesign this as a recursive function (I taught recursive programming design many moons ago) but I'm still not clear how recursion works in T-SQL and this solution, while perhaps not the most elegant, accomplishes the same task without cursors (YAY!!).
Thanks to all who contributed.
- Insert Into @temp
-
OK, next question:
I built a view to test using the above udf and this works:
SELECT mail, sn, givenname
FROM dbo.udf_GetEMpsBYManager('115898') udf_GetEMpsBYManager
Now, I don't want to be hardcoding employeeid's so I wrote a scalar udf, udf_GetEmployeeID that takes a full or partial name and returns a 6 character employeeid. I tried to code that into the view, i.e.:
SELECT mail, sn, givenname
FROM dbo.udf_GetEMpsBYManager(dbo.GetEmployeeid('Schaefer'))udf_GetEMpsBYManager
But I get an error message:
Error in list of function arguments: '.' not recognized.
Incomplete parameters list.
Unable to parse query text.
Can I call a scalar udf in the invocation of a table udf in a view?
There's a good one for you, huh?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply