Thursday, March 8, 2012

Can''t Create Recursive Function

Greetings.

I'm having trouble creating a recursive function in T-SQL (SQL Server 2000).

I've got a table that has an ID column and a ParentID column. Each row can have a value in the ParentID column that references the ID column of another record - I'll call such rows "child records". I'll cal the row referenced by the ParentID the "parent record".

Each child record can itself have another child record.

I need a function that will take an ID column value as a parameter, and walk up the chain of parent records until I get the first record in the series and return that record's ID value. I'll call that record the "UrParent record".

I'm trygin to create a recursive function called ufunc_ST_GetUrParentCertNum. In the function, there is of course a recursive call to itself - GetUrParentCertNum. However, when I try to run the CREATE FUNCTION script, I get the error:

Server: Msg 195, Level 15, State 10, Procedure ufunc_ST_GetUrParentCertNum, Line 26
'ufunc_ST_GetUrParentCertNum' is not a recognized function name.

I tried the same thing with a Stored Procedure, and that worked fine. However, I really want this to work as a function.

Does anyone have advice on how I can achieve this?

Thanks in advance.

- will f

Did you forget to put the schema name before the function name,

try to call your function like dbo.ufunc_ST_GetUrParentCertNum

|||

That did the trick exactly. Thank you very much Smile

- will f

No comments:

Post a Comment