Parameter Defaults
-
Comments posted to this topic are about the item Parameter Defaults
-
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' -
Typo in answers: sys.modules is wrong, as it stated in the explanation: sys.sql_modules.
- 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.
-
On my SQL 2019 box, this def returns the default values.
-
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.
- Carlo Romagnano wrote:
Typo in answers: sys.modules is wrong, as it stated in the explanation: sys.sql_modules.
Thanks, fixed
-
The docs note this has default values for SQLCLR procs, not T-SQL ones.
SQL 2019:
SQL 2022
Docs, note has_default_value
-
It's crazy these aren't parsed and maintained in a DMV for T-SQL procs, but they're not
-
In my opinion, the answer given as correct one, is wrong. Stored Procedures Parameters have to be read from sys.parameters.
-
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply