problem when using temp table to hold data that return from another procedure

Hello,

We have a Query-Hierarchy procedure take two personID and return the persons that in between of them if they are in the same hierarchy. The procedure works fine. But this procedure was called in many other procedures. We use a temp table to hold the data that return from Query_Hierarchy:

create Table #TEMPRESOURCEHI(ResourceID int, Firstname varchar(50), Secondname varchar(50))

insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

The problem is, we have some procedures using such code to get data, and most of them work. 2 years ago, only one procedure is not working, though Query_Hierarchy return correct resutls which have more than one rows, the temp table only contain one row. And later on, some other procedures get the same problem as well, I know some of them are working before, now is broken as well, the temp table always only get one row inserted no even Query_Hierarchy return more than one rows.

For example, after
insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

If I select * from #TEMPRESOURCEHI so I can get the result in query analyzer, it contains one row.
I also try directly call Query_Hierarchy to let the result show in query anaylyzer as well, it return correctly with more than one row.

We have no idea why such problem occurs and why occurs randomly, we can't see any problem from our code and hope someone can have a look and give some suggestions...

Thanks in advanced

The Query_Hierarchy code is as below:
========================================
CREATE PROCEDURE dbo.Query_Hierarchy
@.FirstID int, --ResourceID of the 'leaf'
@.LastID int --ResourceID of the ancestor
AS
DECLARE @.S TABLE (ResourceID int, FirstName VARCHAR (50), LastName VARCHAR (50))
DECLARE @.R TABLE (ResourceID int)

INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT Resource.[ID], Person.FirstName, Person.LastName
FROM Resource
INNER JOIN Person ON PersonID = Person.[ID]
WHERE Resource.[ID] = @.FirstID)
WHILE (@.@.ROWCOUNT > 0)
BEGIN
INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT ParentID, Person.FirstName, Person.LastName
FROM @.S
INNER JOIN ResourceAssociation ON ResourceID = ChildID
INNER JOIN Resource ON ResourceAssociation.ParentID = Resource.[ID]
LEFT OUTER JOIN Person ON Resource.PersonID = Person.[ID]
WHERE ParentID NOT IN (SELECT ResourceID FROM @.S) AND ParentID <> @.LastID)
END

INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT Resource.[ID], Person.FirstName, Person.LastName
FROM Resource
LEFT OUTER JOIN Person ON PersonID = Person.[ID]
WHERE Resource.[ID] = @.LastID)

--Now walk the tree from the top down and filter out any nodes that don't belong to the root given by @.LastID. Easy.
INSERT INTO @.R (ResourceID)
VALUES (@.LastID)
WHILE (@.@.ROWCOUNT > 0)
BEGIN
INSERT INTO @.R (ResourceID)
(SELECT ChildID FROM @.R INNER JOIN ResourceAssociation ON ResourceID = ParentID
WHERE ChildID NOT IN (SELECT ResourceID FROM @.R))
END

SELECT ResourceID, FirstName, LastName FROM @.S
WHERE ResourceID IN (SELECT ResourceID FROM @.R)
AND FirstName is not null
AND LastName is not null

GO
========================================

Try setting the NOCOUNT ON at the beginning of the procedure that loads the temp table. I ran into a similar situation a couple of years ago loading recordsets and only getting one result. When we set nocount on the problem resolved.

Code Snippet

SET NOCOUNT ON

create Table #TEMPRESOURCEHI(ResourceID int, Firstname varchar(50), Secondname varchar(50))

insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

Try this out and see if it resolves your issue. If not try setting the NOCOUNT ON in the ResourceDB.dbo.Query_Hierarchy stored procedure.