CODEWITHSUNDEEP
sql-servervisual-studio-2008deploymentsqlclrsql-server-data-tools
Tao
Tao

Reputation: 14006

SQL CLR Procedure Default Parameter in VS2008 deployment?

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

Upvotes: 8

Views: 1687

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

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.

Upvotes: 1

pirho
pirho

Reputation: 3042

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.

Upvotes: 0

Related Questions