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
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    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 →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • 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