Finding stored procedure defaults for parameters

• tshad

  SSCertifiable

  Points: 5905

  More actions

  June 2, 2014 at 3:01 pm

  #287078

  I have various ways of getting the parameters of a stored procedure:

  I have a procedure that has all defaults 4 are null and 2 are 0.

  The following shows most of what I need but no defaults

  SELECT PARAMETER_NAME ,

  ORDINAL_POSITION ,

  DATA_TYPE ,

  CHARACTER_MAXIMUM_LENGTH ,

  CHARACTER_OCTET_LENGTH ,

  NUMERIC_PRECISION ,

  NUMERIC_PRECISION_RADIX

  FROM information_schema.parameters

  WHERE specific_name = 'procedure'

  This one has two values: has_default_value (which always is 0) and default_value (which always shows null)

  SELECT *

  FROM sys.procedures sp

  JOIN sys.parameters parm ON sp.object_id = parm.object_id

  JOIN sys.types typ ON parm.system_type_id = typ.system_type_id

  WHERE sp.name = 'procedure'

  This one has two values: PARAMETER_HASDEFAULT (always 0) and PARAMETER_DEFAULT (always 0)

  sp_procedure_params_rowset proc procedure

  Is there something else that would tell me if there is a default on a parameter and what the default is if there is one.

  Thanks,

  Tom

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  June 2, 2014 at 9:08 pm

  #1718400

  Not without dissecting the code as only CLR procedure parameter defaults are listed in sys.parameters. More can be found here http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=234143%5B/b].

  😎

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  June 3, 2014 at 6:58 am

  #1718510

  @tshad,

  Interesting... this is the second time in a week that someone has asked this very rare question. I'm just curious... is this a school assignment or are you working on a project with a fellow that goes by the handle of "ramana" on this site?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)