SP default parameters have wrong values or are NULL

ewhipple

When the option "Inserted Code" -> "Objects & Statements" -> "EXEC Statements" -> "Insert default value for each parameter" is enabled, inserted default values have either the wrong value or are inappropriately NULL.

For example, for these three stored procedures (bodies omitted for brevity):

CREATE PROCEDURE testproc1 @parm1 BIT = 1, @parm2 int = NULL

CREATE PROCEDURE testproc2 @parm1 BIT = 1, @parm2 BIT = 0, @parm3 BIT = NULL

CREATE PROCEDURE testproc3 @parm1 VARCHAR(MAX) = 'Hello, World!', @parm2 INT = NULL, @parm3 BIT = 1


Here are the insertions:

EXEC testproc1
@parm1 = NULL, -- bit
@parm2 = 0 -- int

EXEC testproc2
@parm1 = NULL, -- bit
@parm2 = NULL, -- bit
@parm3 = NULL -- bit

EXEC testproc3
@parm1 = '', -- varchar(max)
@parm2 = 0, -- int
@parm3 = NULL -- bit



SQL Prompt 6.0.0.411 in SSMS 2012 (11.0.3128.0)

Aaron L

Hi ewhipple,
We currently have a uservoice request for this feature so please vote add your vote to the idea (we try to prioritise the higher voted ideas when deciding what to do next)

Unfortunately this is one of those features where it feels like it should be really simple but ends up being rather complicated as SQL Server doesn't have an easy way to retrieve these values (sys.parameters and sp_procedure_params_rowset both look like they should but only give NULL for non-clr procedures)