Strange behaviour of System UDFs

• Amit Jethva

  SSCarpal Tunnel

  Points: 4707

  More actions

  January 22, 2004 at 11:17 pm

  #65026

  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/default.asp?url=/library/en-us/dnsqlpro01/html/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

   

  
  

  -- Amit

  ---

  
  "There is no 'patch' for stupidity."

  ---

  
  Download the Updated SQL Server 2005 Books Online.

• January 26, 2004 at 8:00 am

  This was removed by the editor as SPAM

• mccork

  SSC-Insane

  Points: 22288

  More actions

  January 28, 2004 at 2:17 am

  #491893

  If it's any consolation, existing system functions exhibit the same behavior.  Eg.

  create table x

  (id int identity,

  name varchar(30) constraint checkname check (fn_chariswhitespace(name) <> 1))

  Server: Msg 195, Level 15, State 10, Line 3

  'fn_chariswhitespace' is not a recognized function name.

   

  I haven't seen a workaround.... yet.

  
  Cheers,
  - Mark