VS2008部署中的SQL CLR过程默认参数？
我知道我可以在数据库中创建过程时为 CLR 过程定义默认值，如下所示：
CREATE PROCEDURE [dbo].[ShredXml] (
@InputXml [xml],
@AttributeElementHandling [tinyint] = 0,
@ConversionHandling [tinyint] = 0,
@RootElementName [nvarchar](255) = null
)
AS EXTERNAL NAME [ClrXmlShredder].[ClrXmlShredder].[ShredXml]
我无法弄清楚是否有任何方法可以说服 Visual Studio 在使用其“部署项目”选项时自动执行此操作...
是否有一个可以在参数上设置的属性，以告诉 Visual Studio 当它在数据库中创建过程时您希望该参数的默认值是什么？
更新：我尝试设置可空性“SqlFacet”，这似乎没有效果（我猜这是有道理的 - afaik 存储过程参数总是可以为空？）
[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
)
{
}
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
)
{
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不幸的是，截至今天，答案是响亮的“不”。 SSDT 不支持相当多的选项，例如为标量 UDF 指定
WITH RETURNS NULL ON NULL INPUT等。我打开了一个 Connect Suggestion 来支持参数默认值，SSDT - 在生成时通过 SqlFacet 属性支持 SQLCLR 对象的 T-SQL 参数默认值发布并创建 SQL 脚本，但到目前为止的官方说法是：“好主意，但不会很快发生”。
因此，目前您最好的选择是创建一个部署后脚本（在 SQL Server/用户脚本中找到）并添加
ALTER语句（如果使用“生成 DDL”选项）或CREATE语句（如果不使用“生成 DDL”选项）可根据需要重新定义存储过程和/或函数属性。部署后脚本将附加到生成的部署脚本的末尾。
我还在致力于解决 SSDT 发布过程中的这个漏洞，并允许以编程方式设置这些选项。如果我让它工作，我会用详细信息更新这个答案。
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 INPUTfor 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
ALTERstatements (if using the "Generate DDL" option), orCREATEstatements (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.
您可以在函数中定义默认值。当从底层 SQL 端包装器传递 null 时，将使用它们。只需声明没有默认值的过程参数并将默认值放入函数即可。应该没问题。
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.