Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SSMS scripting CLR stored procedure NVARCHAR parameter NULL default as N'' (empty string)

When scripting out a SQLCLR stored procedure in SSMS, if there is an NVARCHAR parameter that was given a default value, and that default is "= NULL ", then it will be scripted out as "= N'' ".

For an easy example, I am using the DB_BulkCopy stored procedure that is available in the Free version of SQL# (SQLCLR library that I created -- http://SQLsharp.com/ ). This stored procedure has several NVARCHAR parameters that are defaulted to NULL.

If you script that stored procedure out, you will get:

CREATE PROCEDURE [SQL#].[DB_BulkCopy]
@SourceType [nvarchar](4000) = N'',
@SourceConnection [nvarchar](4000) = N'',
@SourceQuery [nvarchar](4000),
@DestinationConnection [nvarchar](4000) = N'',
...

However, if you execute the following:

SELECT [default_value], *
FROM sys.parameters
WHERE [object_id] = OBJECT_ID(N'SQL#.DB_BulkCopy')
ORDER BY [parameter_id];

You will see the NVARCHAR parameters showing up with "NULL" in the [default_value] field.

1 vote
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

Solomon Rutzky shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    I just tested this in SSMS 18.0 RC1 (SQL Server Management Studio 15.0.18098.0) and it is still a problem. Please fix! And, wouldn't this also affect SMO?

  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    To be clearer about the parameters that have default values, the following query filters out parameters that do not have defaults (else it is more difficult to see those that have NULL for the value simply because no default value was ever set):

    SELECT [default_value], *
    FROM sys.parameters
    WHERE [object_id] = OBJECT_ID(N'SQL#.DB_BulkCopy')
    AND [has_default_value] = 1
    ORDER BY [parameter_id];

    ALSO, since the DB_BulkCopy stored procedure currently has non-NULL defaults for the INT parameters, I just changed one to NULL to see if it was only NVARCHAR parameters that are affected by this bug. After changing the @NotifyAfterRows parameter to have a default value of NULL, it was scripted out as follows:

    @NotifyAfterRows [int] = ,

    which produces a syntax error, unlike using N'' in place of NULL for the NVARCHAR parameters. So yes, this bug affects more than just NVARCHAR parameters.

Feedback and Knowledge Base