Professional Web Applications Themes

Q: retrieving default values for parameters in stored procedures ? - Microsoft SQL / MS SQL Server

Hi, just wondered if anyone here knows a way to programtically retrieve the default values (if any) of the parameters of a given proc ? Tool to retrieve is not mportant (SQLDMO, SQL, ADOX, etc). Example: create proc proc_test param1 int, param2 int = 3, param3 int out as begin set param3 = param1 + param2 return 0 end in this example, I can by use of syscolumns or the PARAMETERS view find out all the parameters, direction (in/out), and datatype. I can not however get the default value of param2 (=3 in this case). I am working on parsing ...

  1. #1

    Default Q: retrieving default values for parameters in stored procedures ?

    Hi,

    just wondered if anyone here knows a way to programtically retrieve the
    default values (if any) of the parameters of a given proc ? Tool to retrieve
    is not mportant (SQLDMO, SQL, ADOX, etc).

    Example:

    create proc proc_test
    param1 int,
    param2 int = 3,
    param3 int out
    as
    begin
    set param3 = param1 + param2
    return 0
    end

    in this example, I can by use of syscolumns or the PARAMETERS view find out
    all the parameters, direction (in/out), and datatype. I can not however get
    the default value of param2 (=3 in this case).

    I am working on parsing this out of the text column in syscomments,
    alternatively using the Command property of the StoredProcedure2 object in
    SQLDMO. But parsing this information is a PITA, mainly ue to the fact that
    you need to know whether you're in a comment block or not, that this is
    really the parameter definition etc.

    -Working on a TSQL beautifier/doentation tool..

    Any help hints appreciated :-)

    Respectfully,

    Fridhtjof-G Eriksen


    Fridthjof-G Guest

  2. #2

    Default Re: retrieving default values for parameters in stored procedures ?

    Hi Fridthjof,

    AFAIK parsing syscomments is the only way to get the parameter defaults.
     
    I hope you are not doing this on a commercial basis, because there are too
    many of these tools out there already for a new one to have a chance of
    success, not to mention the new client tools that will come with the next
    version of SQL Server sometime next year. I intend to switch to those tools
    the moment the public beta is released, even though we won't upgrade our
    databases until RTM.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Fridthjof-G Eriksen" <com> wrote in message
    news:phx.gbl... 
    retrieve 
    out 
    get 


    Jacco Guest

  3. #3

    Default Re: retrieving default values for parameters in stored procedures ?

    As I feared then in other words..

    As for commercially available beautifiers, actually not many out there that
    are good (for TSQL, for PL/SQL you have a lot of good options), at least not
    that I've come across (and I've searched high and low).

    But no, it is not a commercial project, but primarily to aid me in my
    development, esepcially the doentation part. There are 5 places the
    doentation must go for all objects, the header of the .sql file, the
    model (ER Studio), the online .chm help file, extended properties (meta
    data), and a printable doent. This is a tedious task, and should of
    course be automated :-)

    I hope to get a look at what to expect from the new client tools of the next
    release, attending a yukon 2 day presentation in a few weeks.

    Fridthjof

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    > I hope you are not doing this on a commercial basis, because there are too
    > many of these tools out there already for a new one to have a chance of
    > success, not to mention the new client tools that will come with the next
    > version of SQL Server sometime next year. I intend to switch to those[/ref]
    tools 
    > retrieve 
    > out 
    > get [/ref]
    in [/ref]
    that 
    >
    >[/ref]


    Fridthjof-G Guest

Similar Threads

  1. Retrieving url string / parameters
    By Matheus in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 10th, 04:37 PM
  2. Retrieving url string / parameters
    By Fiffen in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 10th, 03:25 PM
  3. PHP and returned values from Informix stored procedures
    By Steve Weet in forum PHP Development
    Replies: 4
    Last Post: November 6th, 04:09 PM
  4. DDL values with DataReader and stored procedures
    By Rob Wire in forum ASP.NET General
    Replies: 5
    Last Post: August 14th, 09:50 AM
  5. Extended Stored Procedures accepting Real Parameters
    By Microfirm in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 09:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139