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
    )
{
}

Donnetta

Did you ever happen to find a solution to this? – Sum

@Sum no, I didn't, but then honestly I also stopped worrying about this more than 2 years ago, with VS2008; I don't know whether anything has changed in later versions. – Donnetta

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.

Renaldorenard

Hi @Solomon, Has this been fixed in the latest version? – Roxanneroxburgh

Answer 2

You can define the default values in the function. They will be used when null is passed from underlying SQL-side wrapper. Just declare procedure parameters without defaults and put defaults to the function. It should be OK.

Esch

Thanks, but this is not really what I'm asking. If I do as you say, then the caller to my proc needs to say something like "EXEC ShredXml @Value, null, null, null". I would like them to be able to just say "EXEC ShredXml". Not a big deal/design problem, as there's a workaround with manual publishing - just a question about the visual studio automated deployment. – Donnetta