Options
SP default parameters have wrong values or are NULL
ewhipple
Posts: 10
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)
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)
Comments
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)