I am trying to create a generic table valued function which returns all child IDs for a specified parent of any self join table.

The function is passed the table name, PK column name, parent column name and the ID value and returns a single column table with all the child IDs (including all descendants down to the leaf level of the self join hierarchy).

The cursor works fine on its own to retrieve the child IDs, but I'm having problems including it in the function definition. See SQL below:


Code:
CREATE FUNCTION getChildren 
(@TableName varchar(30), @PKColumnName varchar(30), @ParentColumnName varchar(30), @PKValueId decimal(9))

RETURNS @resultsTable table (PKValueId numeric(9))
AS

BEGIN
declare 
@CurrentPKValueId as decimal(9),
@Table as varchar(30),
@PKColumn as varchar(30),
@ParentColumn as varchar(20)

SET @Table = @TableName
SET @PKColumn = @PKColumnName
SET @ParentColumn = @ParentColumnName

INSERT INTO @resultsTable
SELECT @PKColumn
FROM @Table
WHERE @PKColumn = @PKValueId

DECLARE SelfJoinCursor CURSOR FOR 
SELECT @PKColumn
FROM @Table
WHERE @ParentColumn = @PKValueId

OPEN SelfJoinCursor

FETCH NEXT FROM SelfJoinCursor INTO @PKValueId
WHILE (@@fetch_status <> -1)
BEGIN

INSERT INTO @resultsTable
SELECT @PKColumn
FROM dbo.getChildren(@PKValueId)

FETCH NEXT FROM SelfJoinCursor INTO @PKValueId
END

CLOSE SelfJoinCursor
DEALLOCATE SelfJoinCursor
RETURN 
END
The error states I need to declare the @Table variables again in the cursor, but I'm not sure how. Any suggestions appreciated.