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
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base