Post reply

Determine default values for UDF parameters

  • December 12, 2003 at 8:50 am

    #81700

    I would like to be able to lookup the parameters, along with default values, for any UDF.

    sp_sproc_columns gives me the parameter names and data types - and it is supposed to give me the default value, at least according to:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_7zzn.asp

    However, the COLUMN_DEF field is always NULL, even when I know there is a default value defined. I looked at the text of sp_sproc_columns and it never appears to get any value for COLUMN_DEF ...

    Is there any other reliable way to lookup the default value?

    I've considered using sp_helptext to get the text of the UDF and then try and parse out the default values. But that would be a bit messy and would not work for encrypted functions.

  • December 12, 2003 at 11:42 am

    #486002

    I have some bad news for you,

    YES YOU ARE RIGHT !!!

    COLUMN_DEF IS HARDCODED AS convert(nvarchar(4000),NULL)

    the reason: AFAIK that Default is NOT stored on any table as of SP3 ... maybe SP4

    If you are going to parse it then I would suggest you use:

    

    SELECT 

           ROUTINE_DEFINITION 

    FROM 

           INFORMATION_SCHEMA.ROUTINES

    WHERE 

           ROUTINE_TYPE ='FUNCTION' 

           AND 

           ROUTINE_NAME = @YOUR_FUNCTION

    is may be a little earier than sp_helptext

    HTH


    * Noel

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

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