Table valued function with cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table valued function with cursor

Author

Topic

mikesmithee
Starting Member

1 Post

Posted - 2009-10-28 : 02:50:01

I am trying to create a generic table valued function which returns all children 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:

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources