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

@tbone 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. — Tao
– Tao, Commented May 28, 2015 at 5:24

Solomon Rutzky · Accepted Answer · 2015-08-28 15:07:01Z

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.

answered Aug 28, 2015 at 15:07

Solomon Rutzky

49.3k11 gold badges141 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yendang9423082 Over a year ago

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

pirho · Accepted Answer · 2011-07-13 22:36:58Z

0

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.

answered Jul 13, 2011 at 22:36

pirho

3,0424 gold badges24 silver badges17 bronze badges

1 Comment

Tao Over a year ago

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.

Collectives™ on Stack Overflow

SQL CLR Procedure Default Parameter in VS2008 deployment?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related