SQL CLR Procedure Default Parameter in VS2008 deployment?

Question

I know that I can define default values for CLR procedures when creating the procedure in the database, like this:

CREATE PROCEDURE [dbo].[ShredXml] (
    @InputXml [xml], 
    @AttributeElementHandling [tinyint] = 0,
    @ConversionHandling [tinyint] = 0,
    @RootElementName [nvarchar](255) = null
    )
AS EXTERNAL NAME [ClrXmlShredder].[ClrXmlShredder].[ShredXml]

What I can't figure out is whether there's any way to convince Visual Studio to do this automatically when using its "Deploy Project" option...

Is there an attribute one can set on an argument to tell visual studio what you want the default value for that argument to be, when it creates the proc in the database?

Update: I've tried setting the nullability "SqlFacet", that seemed to have no effect (which makes sense I guess - afaik stored proc params are always nullable?)

[Microsoft.SqlServer.Server.SqlProcedure]
public static void ShredXml(SqlXml InputXml, 
    [SqlFacet(IsNullable = true)]SqlByte AttributeElementHandling, 
    [SqlFacet(IsNullable = true)]SqlByte ConversionHandling, 
    [SqlFacet(MaxSize = 255, IsNullable = true)]string RootElementName
    )
{
}

Answer 1

Is there an attribute one can set on an argument to tell visual studio what you want the default value for that argument to be, when it creates the proc in the database?

As of today the answer is a resounding "no", unfortunately. SSDT does not support quite a few options, such as specifying WITH RETURNS NULL ON NULL INPUT for Scalar UDFs, etc.

I opened up a Connect Suggestion to support parameter defaults, SSDT - Support T-SQL parameter defaults for SQLCLR objects via the SqlFacet attribute when generating the publish and create SQL scripts, but the official word so far is: "great idea, but not gonna happen anytime soon".

So for now your best bet is to create a Post-Deployment Script (found in SQL Server / User Scripts) and add either

• ALTER statements (if using the "Generate DDL" option), or
• CREATE statements (if not using the "Generate DDL" option)

there to redefine the Stored Procedure and/or Function properties as desired. The Post-Deployment Script gets appended to the end of the generated deployment script.

I am also working on something that will hopefully fix this gaping hole in the SSDT publishing process and allow for programatically setting these options. If I get it working I will update this answer with the details.