locked
Calling Schemabinding function recursively in SQL Server 2008 RRS feed

  • Question

  • Hi,

    I have function which is created with SchemaBinding. Now I wanted call this function recursively. But it is giving me the following error.

    Here is my test function

    ALTER FUNCTION [Fn].[getIDsTest]

    (

     @id int

    )

    RETURNS INT  WITH SCHEMABINDING

    As  

    BEGIN  

     

    if @id=1

     SELECT @id=[Fn].[getIDsTest](2)

     

    RETURN 3

    END

    This is the error I am getting when I try to Alter the function by calling recursively

    Msg 4512, Level 16, State 3, Procedure getIDsTest, Line 13

    Cannot schema bind function 'Fn.getIDsTest' because name 'Fn.getIDsTest' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

     

    Please help to resolve this or any idea.


    Thanks

    Sanjay

    Thursday, August 5, 2010 9:18 AM

All replies

  • Hi All,

    This is something which is not possible with SchemaBinding objects(object can not refer itself.)

    In schema binding an Object cannot refer itself. Schema binding means the definition of the object is schema bounded to the objects used in that Object. In this example we are trying to create a schema binding function by calling itself without knowing its existence for its schema bound ( this is called anti deferred name resolution ) . So we cannot have a schema bind for it its ambiguous.

     

    Deferred name resolution ignores the schema definition of an Object at compile time (time of creation)

     

    CREATE PROCEDURE spProcedure as

    SELECT * FROM NonExistencetable

     

    Compiles succefully even if there is no table in DB and this is called Deferred name resolution (Schema binding avoids this)

     

    Thanks

    Sanjay


    Hi,

    I have function which is created with SchemaBinding. Now I wanted call this function recursively. But it is giving me the following error.

    Here is my test function

     

    ALTER FUNCTION [Fn].[getIDsTest]

    (

     @id int

    )

    RETURNS INT  WITH SCHEMABINDING

    As  

    BEGIN  

     

    if @id=1

     SELECT @id=[Fn].[getIDsTest](2)

     

    RETURN 3

    END

    This is the error I am getting when I try to Alter the function by calling recursively

     

     

    Msg 4512, Level 16, State 3, Procedure getIDsTest, Line 13

    Cannot schema bind function 'Fn.getIDsTest' because name 'Fn.getIDsTest' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

     

     

    Please help to resolve this or any idea.


    Thanks

    Sanjay

     

     

     


    Tuesday, August 10, 2010 9:40 AM