Posted by nicolec on 09/07/05 21:46

I've been tearing my hair out over this UDF. The code works within a
stored procedure and also run ad-hoc against the database, but does not
run properly within my UDF. We've been using the SP, but I do need a
UDF instead now.

All users, including branch office, sub-companies and companies and so
on up the lines are in the same table. I need a function which returns
a row for each level, eventually getting to the master company all the
way at the top, but this UDF acts as though it can't enter the loop and
only inserts the @userID and @branchID rows. I have played with the
WHILE condition to no avail.

Any ideas on what I am missing?

(Running against SQL Server 2000)
---------------------------------------------------

ALTER FUNCTION udfUplineGetCompany (@userID int)

RETURNS @upline table (companyID int, companyname varchar(100), info
varchar(100))
AS
BEGIN

DECLARE @branchID int
DECLARE @companyID int
DECLARE @tempID int

--Insert the original user data
INSERT INTO @upline
SELECT tblusersid, companyname, 'userID'
FROM tblusers
WHERE tblusersid = @userid

SELECT @branchID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @userid

--Up one level
INSERT INTO @upline
SELECT tblusersid, companyname, 'branchID'
FROM tblusers
WHERE tblusersid = @branchid

SET @tempID = @branchID

WHILE @@ROWCOUNT <> 0
BEGIN
SELECT @companyID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @tempID
AND tblUsersId <> 6

--Insert a row for each level up
INSERT INTO @upline
SELECT tblusersid, companyname, 'companyID'
FROM tblusers
WHERE tblusersid = @companyID

SET @tempID = @companyID

END

RETURN

END

[Back to original message]