|
|
Posted by Gert-Jan Strik on 09/07/05 22:23
Well, 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
Navigation:
[Reply to this message]
|