optional parameter

  • How to determine which input parameter in a Stored procedure is optional (means have a defaullt value, could be NULL) in SQL 2005

    I can get other information FROM INFORMATION_SCHEMA.PARAMETERS

    and there is a view sys.all_parameters which has field called has_default_value but seems to be incorrect.

    Anyone with any other info ??

    Kindest Regards,

    Amit Lohia

  • Having a default value and being optional are different things entirely.......the view is correct.

    I do not have an answer for where to find the optional designatore though, as I always just look at the proc itself....

  • If I am not mistaken. To make a parameter optional you have to pass a default value or NULL.

    for example

    Create proc ABC


    @Parameter1 INT = NULL



    Kindest Regards,

    Amit Lohia

  • sp_helptext <procname>

Viewing 4 posts - 1 through 3 (of 3 total)

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