SQL Functions and Recursion

Question

Suppose you write a function in SQL Server that conditionally calls itself. If you are writing the function from scratch, finish it, and try to create it, SQL Server complains.

The complaint is that the function you call from your function doesn't exist. Of course it doesn't, it's recursive!

To actually make it work, you have to comment out the recursive call, create the function, uncomment the call, and alter the function. You have to go through this nonsense if you ever change what parameters the function accepts (in this case it complains there are too many or too few parameters in your new recursive call).

Is there any way around this?

Despite the wording, I think this is just one question with two examples of problems caused by the same issue. — JohnFx
– JohnFx, Commented Feb 12, 2009 at 21:24
They're related in the sense that they both annoy me at the same time. I think I'll split them up though. — colithium
– colithium, Commented Feb 12, 2009 at 21:52

JohnFx · Accepted Answer · 2009-02-12 21:19:32Z

7

For stored procedures you should get an error like this, that you can just ignore:

Cannot add rows to sysdepends for the current object because it depends on the missing object 'sub_proc1'. The object will still be created.

For user defined functions it is a little trickier, but it works (at least it did for me on SQL 2k8) if you fully qualify the function name in the recursive call.

CREATE FUNCTION recursiveUDF () RETURNS int
AS
BEGIN

    DECLARE @X int

     --Fails with "recursiveUDF is not a recognized built-in function name."
    SET @X = recursiveUDF()          

     --works!
    SET @X = dbo.recursiveUDF()  

    RETURN 1
END

edited Feb 12, 2009 at 21:19

answered Feb 12, 2009 at 21:04

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DJ. Over a year ago

The create should still work - it's just a warning message that you can ignore

colithium Over a year ago

That does the trick when Creating the function, however, Alter still fails. I think it's an annoyance I'm just going to have to live with.

JohnFx Over a year ago

Why not just do an "if exists..drop" then "Create" instead of an alter? It is slightly more effort, but isn't so terrible.

Collectives™ on Stack Overflow

SQL Functions and Recursion

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related