Check if SQL parameter is optional.

  • Peter Mallegrom

    Peter Mallegrom - 2009-02-24


    I have some stored procedures for which I want to generate the calling code.
    For this I need to be able to define whether a given parameter in the stored procedure is optional (has a default value: "@Test INT = 0") or not.
    I can't seem to find this in the meta properties of the parameters.
    Am I overlooking something?


    • Paul Jacobson

      Paul Jacobson - 2009-02-25

      I'm struggling with the same issue.  I'm using MyGeneration with MyMeta with a SQL 2005 database.  I have an update stored procedure with a mixture of optional and required parameters.  The IParameter.IsNullable property appears to always show true; furthermore, the IParameter.HasDefault is always false and IParameter.Default is always blank.  The procedure is very basic.  Does anyone have a solution?


    • Peter Mallegrom

      Peter Mallegrom - 2009-03-02

      The only way I found was using the ProcedureText property of a procedure and check the complete procedure for the definition of the parameter.

  • Todd W. Powers

    Todd W. Powers - 2009-09-30

    Hi pmallegrom,

    I had this same issue a couple of years ago.  At the time, we determined that the problem was caused by the fact that MSSQL (the database I was working with at the time) didn't expose information about the defaults for procedure parameters.

    I wound up writing an add-in to MyGeneration, that parsed the full text of the stored procedure, to pull out the default values and populate them into the User MetaData.  I then used those custom metadata fields for my scripts.

    Unfortunately, I have had a hard-drive crash and have lost all of that work.  I am unable to locate the original source for that add-in.

    That is why I'm here actually…  To find out if the problem had ever been resolved in the official release.  Upon examining the MetaData from v1.3, I can see that this problem still exists.

    I really hate doing work twice, so I'm going to spend some more time, trying to find out if I can locate the original source for my plug-in.  It wasn't elegant, but it worked.

    If I can't find it, I suspect I am going to have to do the work all over again.


Log in to post a comment.