Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Red face Unanswered: Strange behaviour of System UDFs

    Hi,

    I have written an udf that checks for validations of an email address and returns 0 / 1 as per the validations. I came across an artical on MSDN that describes the steps to make my own system udf. http://msdn.microsoft.com/library/de...ml/sql01l1.asp

    with help of that artical, I made my udf as a system udf. and now I can use it as follows.

    select * , fn_isvalidemail(email) from #tp

    now, I wanted to use this as a declarative check constraints in my tables. so I tried

    create table testtable
    ( email_addr varchar (255) null CONSTRAINT CK_email_chk CHECK ( fn_isvalidemail(email) = 1) )

    although this is syntax is logically correct, it does not get compiled

    it gives me an error saying,

    Server: Msg 195, Level 15, State 10, Line 2
    'fn_isvalidemail' is not a recognized function name.


    the strange part is that i made the function as another normal udf, It works in declarative constraints.

    create table testtable
    ( email varchar (255) null CONSTRAINT CK_1p12 CHECK ( dbo.isvalidemail(email) = 1) )


    Any thoughts on this ?

    - Amit

  2. #2
    Join Date
    Jan 2004
    Posts
    10

    Re: Strange behaviour of System UDFs

    No replies yet ? .................

    amit

  3. #3
    Join Date
    Nov 2003
    Posts
    94
    I think the problem is an issue of schema binding. If a function is owned by dbo in the same database as it's use in a dbo table definition, sql server can bind the function to the table's schema, preventing it being changed whilst bound to the table's DDL. Whereas if it's in any other database including master.system_function_schema, it can't be bound.

    If allowed, this would enable a situation where, say a default value could have its underlying value function either change return type or be deleted, leaving the table orphaned.

    I think you must use schemabindable entities in DDL commands, so you must implement your function as part of the dbo space in the database in which you use it.

    If you execute each step of the following:

    CREATE FUNCTION dbo.fn_test
    (
    @i int
    )
    RETURNS int
    AS
    begin
    declare @ret int

    set @ret = @i * 2

    return @ret
    end
    go

    create table thing
    (
    id int default dbo.fn_test(1)
    )

    drop function fn_test
    go

    drop table thing
    go

    drop function fn_test
    go

    You get Cannot DROP FUNCTION 'fn_test' because it is being referenced by object 'DF__thing__id__xxxx on the first attempt to drop.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •