I got it working the way I needed for my app. I'm putting the code here so if someone else is looking for a solution, they will see what I came up with.

Thanks to all who helped!

CREATE PROCEDURE dbo.spGetHierarchy
(
@FamilyName REAL
)
AS

DECLARE @Parent_id INTEGER

CREATE TABLE #tbParent
(
    [ParentId] [int] NOT NULL ,
    [FamilyName] [varchar] (20) NOT NULL
)

CREATE TABLE #tbChildren
(
    [ChildId] [int] NOT NULL ,
    [ParentId] [int] NOT NULL ,
    [ChildName] [varchar] (10) NULL
)

DECLARE curParent CURSOR FOR
  SELECT ParentId
    FROM tbParent
    WHERE FamilyNm = @FamilyName
    ORDER BY ParentID

OPEN curParent

FETCH NEXT FROM curParent INTO @ParentId
WHILE (@@FETCH_STATUS = 0)
  BEGIN
    INSERT #tbParent
      SELECT
        ParentId,
        FamilyNm
        FROM tbParent
        WHERE ParentId = @ParentId
        ORDER BY ParentId

    INSERT #tbChildren
      SELECT
        ChildId,
        ParentId,
        ChildNm
        FROM tbChildren
        WHERE tbParent.ParentId = @ParentId

    FETCH NEXT FROM curParent INTO @ParentId

  END

CLOSE curParent
DEALLOCATE curParent

SELECT
   ParentId,
   FamilyNm
  FROM #tbParent

SELECT
  ChildId,
  ParentId,
  ChildNm
  FROM #tbChildren

DROP TABLE #tbParent
DROP TABLE #tbChild
GO

Kudos for figuring out how to use cursors. Only thing is that in this case, they are a major overkill. Now that I see what the result is you want, I have to report that you can get the same result in a much easier way:

CREATE PROCEDURE dbo.spGetHierarchy
(
@FamilyName REAL
)
AS

SELECT ParentId, FamilyNm
FROM tbParent
WHERE FamilyNm = @FamilyName
ORDER BY ParentID

SELECT c.ChildId, c.ParentId, c.ChildNm
FROM tbChildren AS c
JOIN tbParent AS p ON c.ParentID=p.ParentID
WHERE p.FamilyNm = @FamilyName
GO

This will give you the tbParent rows and tbChildren rows for the provided "FamilyName". Keep in mind that the results from this nor your original sproc are represented hierarchically. That representation will be up to the consumer of the data.

Had I realized what you were looking for in the beginning, I wouldn't have led you down the dark forest path of SQL cursors.

Perhaps a better suggestion from me would have been that you do a little reading on JOINs.

One of the important things to remember when dealing with any database is that you should think of the data in terms of sets. You can certainly have several sets that are related to each other on a row to row basis and you have to write queries that take this into consideration. But the actual "outcome" (if you will) of the relationship (i.e. Row A in this table relates to Rows X, Y & Z in that table) is the concern of the consuming application.

Peter
------------------------------------------------------
Work smarter, not harder.

Peter, thanks so much for your patience and understanding. I appreciate learning 'better ways of doing things'.

I was able to easily get the relationships/hierarchy to work in VB.NET, so I was happy. That I have to say was the easy part. I'm even happier to know of a better way to do my stored proc.

Thanks again and have a Happy New Year!
SP