Post reply

How to use System Functions as Default Constraints or Computed Columns?

  • June 2, 2004 at 12:10 pm

    #161593

    Does anyone know how to use a System Function as a Default Constraint or Computed Column?

    The closest article that I've found was 

    http://www.15seconds.com/issue/000817.htm

    In it, it mentions how to call system functions, but you can't use any of the syntaxes that are listed to use a system function as a default constraint or a computed column. Even if you use the

    system_function_schema as the owner, it doesn't work.

    This is what I've tried but with no such luck…:

    create table dbo.test( test bit default fn_chariswhitespace(' ') )

    create table dbo.test( test bit default ::fn_chariswhitespace(' ') )

    create table dbo.test( test bit default system_function_schema.fn_chariswhitespace(' ') )

    create table dbo.test( test bit default dbo.fn_chariswhitespace(' ') )

     

    System functions that return a table have the following syntax:

    ::function_name ([argument_expr], [,...])

    System functions that return a scalar value use this syntax:

    function_name ([argument_expr], [,...])

    User-created scalar and rowset functions are invoked in exactly the same

    manner. The syntax for invoking a user-created function looks like this:

    [database_name] owner_name. function_name ([argument_expr], [,...])

     

    Some of you might be wondering why in the world would anyone want to do this.  Well, I have a bunch of UDF's that I want to make easier to maintain in one location and easier to call by converting them to System Functions.  I also use a few of these UDF's as Default Constraints a lot, and when I was testing to see if they would work I kept coming up with errors.  Then I tried MS's own stuff the same way and i got the same.  Now, I'm stuck...

    Thanks,

    Brad

    --pulling my hair out crazy in oklahoma--


    Brad

  • June 2, 2004 at 4:06 pm

    #508744

    Well, I just found out that it can't be done...atleast not with SQL2K.

    Here's an excerpt from BOL.

    DEFAULT definitions

    User-defined functions can be invoked as the constant_expression of DEFAULT definitions if the argument values passed to the function contains only constants. The owner of the table must also be the owner of the user-defined function invoked by a DEFAULT definition on the table.

    Since a system function does not have the same owner as the table, it won't work.  In addition, you also can't create a default that references a UDF from a different DB even when the owner is dbo.  This is because dbo from one DB is not the sam user as dbo in a different DB.


    Brad

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply