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

Support default parameter values for NVARCHAR(MAX), VARBINARY(MAX), and XML types in SQLCLR objects

While it is possible to specify default parameter values for the NVARCHAR(MAX), VARBINARY(MAX), and XML datatypes when creating T-SQL stored procedures and functions, it is not allowed when creating the T-SQL wrapper objects for SQLCLR methods. Attempting to add a default for a parameter of any of these types results in the following error:

Msg 1096, Level 16, State 2, Procedure EncodingTest, Line 2
Default parameter values for CLR types, nvarchar(max), varbinary(max), and xml are not supported.

However, the following does work:

CREATE PROCEDURE dbo.MaxParamDefault
(
@Bob NVARCHAR(MAX) = 'yo'
)
AS
SELECT @Bob AS [ThisIsTheBob];
GO

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

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) 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...

      Feedback and Knowledge Base