What are the challenges you face when working across database platforms? Take the survey

SP default parameters have wrong values or are NULL

ewhippleewhipple Posts: 10
edited September 12, 2013 12:03PM in SQL Prompt
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 in SSMS 2012 (11.0.3128.0)


  • Options
    Aaron LAaron L Posts: 596 New member
    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)
Sign In or Register to comment.