Default values for stored procedure not assigned correctly

ggeier

When Prompt fills in the default values for the parameters, they are not correct when the default is NULL for the parameter.

For example:

create procedure dbo.DataTypes
	 @bigint bigint = null
	,@binary binary = null
	,@bit bit = null
	,@char char(1) = null
	,@date date = null
	,@datetime datetime = null
	,@datetime2 datetime2 = null
	,@datetimeoffset datetimeoffset = null
	,@decimal decimal = null
	,@float float = null
	,@geography geography = null
	,@geometry geometry = null
	,@hierarchyid hierarchyid = null
	,@int int = null
	,@money money = null
	,@nchar nchar(1) = null
	,@numeric numeric = null
	,@nvarchar nvarchar(1) = null
	,@real real = null
	,@smalldatetime smalldatetime = null
	,@smallint smallint = null
	,@smallmoney smallmoney = null
	,@sql_variant sql_variant = null
	,@sysname sysname = null
	,@time time = null
	,@timestamp timestamp = null
	,@tinyint tinyint = null
	,@uniqueidentifier uniqueidentifier = null
	,@varbinary varbinary = null
	,@varchar varchar(1) = null
	,@xml xml = null
as
begin
	return 0
end

Produces this EXEC statement:

exec dbo.DataTypes
    @bigint = 0
   ,@binary = null
   ,@bit = null
   ,@char = ''
   ,@date = '2016-07-08 11:34:12'
   ,@datetime = '2016-07-08 11:34:12'
   ,@datetime2 = '2016-07-08 11:34:12'
   ,@datetimeoffset = '2016-07-08 11:34:12'
   ,@decimal = null
   ,@float = 0.0
   ,@geography = null
   ,@geometry = null
   ,@hierarchyid = null
   ,@int = 0
   ,@money = null
   ,@nchar = N''
   ,@numeric = null
   ,@nvarchar = N''
   ,@real = 0.0
   ,@smalldatetime = null
   ,@smallint = 0
   ,@smallmoney = null
   ,@sql_variant = null
   ,@sysname = null
   ,@time = null
   ,@timestamp = null
   ,@tinyint = 0
   ,@uniqueidentifier = null
   ,@varbinary = null
   ,@varchar = ''
   ,@xml = null

Many of the default values not null when they should be. Is there a way to have SQL Prompt fill in the correct values?

edit: formatting

edit2: I did not test all possible scenarios, the above is just what stuck out the most.

That said, there is also an issue with defaults with the datatype BIT when a non-null default is specified.

create procedure dbo.AnotherTest
	@test bit = 0
as
begin
	return 0
end

Yields:

exec dbo.AnotherTest
    @test = null

Aaron L

Hi ggeier,

Thanks for your post. This is definitely a nice idea unfortunately it's not quite as simple as it looks as there's no view or table to query that we know of to retrieve these default values from SQL Server (If you know of any, please do let me know!).

With that said, we have recently been investigating other ways of retrieving the default values so depending on how that investigation goes we may be able to include this as an experimental feature in SQL Prompt.

We've got a related UserVoice request here. If you add your vote to the UserVoice request you'll get a notification if we release a new build with this implemented.

Thanks,
Aaron.