Multi-table UDF not returning all rows

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

ENDWell, if I try to run in as an ad-hoc query, it doesn't run properly at
all.

The use of @.@.rowcount is bound to go wrong. Also, the INSERT into the
table variable will be executed regardless of a match (or miss) of the
parent selection.

You should check the @.@.rowcount immediately after the "SELECT @.CompanyID
= ..." statement. If you do not want to handle the @.@.rowcount result at
that point, then make sure you move the @.@.rowcount status to a variable
immediately after the SELECT statement.

Gert-Jan

P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

nicolec@.octitle.com wrote:
> 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|||Sometimes you look at something SO long you don't see the obvious...
thanks for the pointer, that worked perfect. For future newsgroup
reference, the new function text is at the bottom.

>P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

No keys anywhere in this database and it's driving me nuts. Every join
must be explicit.

----------------

ALTER FUNCTION udfUplineGetCompany (@.userID int)

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

DECLARE @.branchID int
DECLARE @.companyID int
DECLARE @.tempID int
DECLARE @.rows int

SET @.rows = 1

INSERT INTO @.upline
SELECT tblusersid, companyname, 'userID', @.rows
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

INSERT INTO @.upline
SELECT tblusersid, companyname, 'branchID', @.rows
FROM tblusers
WHERE tblusersid = @.branchid

SET @.tempID = @.branchID

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

SET @.rows = @.@.rowcount

INSERT INTO @.upline
SELECT tblusersid, companyname, 'companyID', @.rows
FROM tblusers
WHERE tblusersid = @.companyID
AND @.rows = 1

SET @.tempID = @.companyID

END

RETURN

END