Parameter Defaults

Question

Parameter Defaults

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
December 20, 2023 at 12:00 am

#4339810

Comments posted to this topic are about the item Parameter Defaults

Viewing 11 posts - 1 through 10 (of 10 total)

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

The default values for parameters are not stored anywhere in the DMVs.
This statement is incorrect

SELECT ProcName = pr.name
     , ParamName = p.name
     , p.has_default_value
     , p.default_value
FROM sys.procedures AS pr
INNER JOIN sys.parameters AS p ON pr.object_id = p.object_id
WHERE pr.name = 'YourProcedureNameHere'

How to post data/code on a forum to get the best help.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 2

Typo in answers: sys.modules is wrong, as it stated in the explanation: sys.sql_modules.

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 3

DesNorton wrote:

The default values for parameters are not stored anywhere in the DMVs.
This statement is incorrect

SELECT ProcName = pr.name
     , ParamName = p.name
     , p.has_default_value
     , p.default_value
FROM sys.procedures AS pr
INNER JOIN sys.parameters AS p ON pr.object_id = p.object_id
WHERE pr.name = 'YourProcedureNameHere'

default_value always is NULL and has_default_value always is false. It's strange but true.

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

On my SQL 2019 box, this def returns the default values.

sys.parameters

How to post data/code on a forum to get the best help.

Dominic Versaci-143726 SSCertifiable Points: 5068 More actions · Answer 5

The "correct" answer is wrong per the documentation for sys.parameters.

Column name: default_value

Data type: sql_variant

Description: If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise NULL.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 6

Carlo Romagnano wrote:

Typo in answers: sys.modules is wrong, as it stated in the explanation: sys.sql_modules.

Thanks, fixed

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 7

The docs note this has default values for SQLCLR procs, not T-SQL ones.

SQL 2019:

2023-12-21 10_16_34-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (52))_ - Microsoft SQL Server

SQL 2022

2023-12-21 10_16_18-SQLQuery1.sql - ARISTOTLE_SQL2022.sandbox (ARISTOTLE_Steve (90))_ - Microsoft SQ

Docs, note has_default_value

2023-12-21 10_17_20-sys.parameters (Transact-SQL) - SQL Server _ Microsoft Learn — Mozilla Firefox

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 8

It's crazy these aren't parsed and maintained in a DMV for T-SQL procs, but they're not

far SSCertifiable Points: 6575 More actions · Answer 9

In my opinion, the answer given as correct one, is wrong. Stored Procedures Parameters have to be read from sys.parameters.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 10

Your opinion is wrong. The question asks for defaults for this T-SQL code. The defaults are not stored in sys.parameters. They are only in the code definition in sys.sql_modules.definition.