SQL CLR Default Procedure in VS2008 Deployment?

Question

I know that I can define default values ​​for CLR procedures when creating a procedure in the database, for example:

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

I can't figure out if there is a way to convince Visual Studio to do this automatically when using the "Deploy Project" option ...

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

Update: I tried setting the “SqlFacet” value to a null value, which seemed to have no effect (which makes sense, I think the afaik parameters stored in proc always have the value NULL?)

 [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 that can be set for an argument to tell visual studio that you want the default value for this argument to be when it creates proc in the database?

To date, the answer is a resounding no, unfortunately. SSDT does not support many parameters, such as specifying WITH RETURNS NULL ON NULL INPUT for scalar UDFs, etc.

I opened a Connect clause to support default parameters, SSDT - T-SQL default support for SQLCLR objects using the SqlFacet attribute when creating publications and creating SQL scripts , but the official word so far is: "a great idea, but it won’t happen in soon".

So, now it’s best to create a Post-Deployment Script (found in SQL Server / User Scripts) and add

• ALTER (if the Create DDL option is used) or
• CREATE (if the Create DDL option is not used)

to override the properties of the stored procedure and / or function as desired. A post-deployment script is added to the end of the generated script deployment.

I am also working on what we hope will eliminate this gaping hole during the SSDT publishing process and allow these parameters to be set programmatically. If I get his work, I will update this answer with details.

Answer 2

You can define default values ​​in a function. They will be used when null is passed from the base shell of the SQL side. Just declare the procedure parameters without default values ​​and set the function by default. That should be good.