October 28, 2009 at 2:44 am
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:
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 variable again in the cursor, but I'm not sure how. Any suggestions appreciated.
October 28, 2009 at 3:36 am
@Table in your script is a varchar variable, not a table.
To do what you're after, you would have to create a sql string in code and then execute it with EXEC(@sql_string) or EXEC sp_executesql @sql, but none of these statements are allowed in a function.
As a side note, cursor = evil, try to get rid of them.
Hope this helps
Gianluca
-- Gianluca Sartori
October 28, 2009 at 7:39 am
can you provide table layouts, sample data and expected results?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2009 at 8:52 pm
mikesmithee (10/28/2009)
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:
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 variable again in the cursor, but I'm not sure how. Any suggestions appreciated.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply