Posted by nicolec on 09/07/05 23:07

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

• Next in forum: [ANNOUNCE] Caught on Tape: Bi-Directional EDI to XML Mapping!
• Prev in forum: Re: Multi-table UDF not returning all rows
• Thread view: Re: Multi-table UDF not returning all rows

[Reply to this message]