SQL Functions and Recursion

Question

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

The complaint is that the function that you are calling from your function does not exist. Of course, this is not so, it is recursive!

To make it work, you must comment on the recursive call, create a function, uncomment the call, and change the function. You have to go through this stupidity if you ever change the parameters that the function takes (in which case it complains that there are too many or too few parameters in your new recursive call).

Is there any way around this?

Answer 1

For stored procedures, you should get an error that you can simply ignore:

It is not possible to add lines to sysdepends for the current object because it depends on the missing object 'sub_proc1'. The object will be created.

For custom functions, this is a bit more complicated, but it works (at least for me in SQL 2k8) if you fully qualify the function name in a 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