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?

Answer 1

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