c# - SQLCLR 具有可选参数/默认值的存储过程

Question

既然 C# 支持可选参数，是否有一种方法可以编写 SQL CLR 存储过程，以便从 Visual Studio 发布时使用在 C# 中定义的可选参数在 SQL Server 中创建存储过程？

过去唯一的方法是手动编写包装函数:
Default parameter values/optional parameters for .NET stored procedures in SQL Server 2005

我宁愿避免这种情况，因为它需要编写两次接口(interface)，并在情况发生变化时维护两个接口(interface)。

示例:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void help(string FunctionName = "")
{
    SqlPipe pipe = SqlContext.Pipe;
    pipe.Send("help> " + FunctionName + "<");
}

从 VS2013 发布会产生(在生成的发布脚本中):

CREATE PROCEDURE [dbo].[help] @FunctionName [nvarchar](MAX)
AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];

运行它:

exec help
Procedure or function 'help' expects parameter '@FunctionName', which was not supplied.

但是，如果您手动编写创建过程:

drop procedure [help]
CREATE PROCEDURE [dbo].[help] 
( @FunctionName nvarchar(100) = null)
AS EXTERNAL NAME [YOURDB].[StoredProcedures].[help];

exec help 
help> <

....它现在按预期运行。

Visual Studio 拥有将参数发布为可选参数所需的所有信息，因为 C# 支持它们，所以要么我做错了什么，要么他们只是没有更新发布实现以识别可选参数。

Answer 1

目前无法向 SSDT(SQL Server 数据工具)发布进程指示参数的默认值。是的，这令人沮丧，因为它确实需要额外的步骤来解析和“修复”生成的 .sql 脚本，或者添加执行 ALTER {object} 的部署后脚本 语句来添加缺少的详细信息(其中包括其他内容，例如标量函数的 WITH RETURNS NULL ON NULL INPUT 选项非常方便)。

我创建了一个连接建议来解决这个缺陷:SSDT - Support T-SQL parameter defaults for SQLCLR objects via the SqlFacet attribute when generating the publish and create SQL scripts

与指定参数默认值的可能性相关的两个注意事项:

• 它们不适用于 LOB 类型的 T-SQL 参数 -- NVARCHAR(MAX)、VARBINARY(MAX) 和 XML -- 因为这些数据类型不支持 SQLCLR 对象中的默认值。这是另一个挫折，我已经记录了以下连接建议来解决这个缺陷:Support default parameter values for NVARCHAR(MAX), VARBINARY(MAX), and XML types in SQLCLR objects
• 它们将(并且应该)作为 SqlFacet 的附加属性处理属性。它们不应该从 C# 方法参数默认值派生的原因是参数默认值在 C# 和 T-SQL 之间的工作方式不同。不仅有上面提到的LOB类型限制，在T-SQL中任何有默认值的参数的位置都是不相关的，有默认值的参数后面可以跟需要的参数(即不指定默认值)。相反，在 C# 中，可选参数必须位于参数列表的末尾，并且后面不能跟必需的参数。正如它在 Named and Optional Arguments 的 MSDN 页面中所述:

  Optional parameters are defined at the end of the parameter list, after any required parameters. If the caller provides an argument for any one of a succession of optional parameters, it must provide arguments for all preceding optional parameters.

• 我目前正在研究一种记录这些默认值的方法，希望 CREATE PROCEDURE 和 CREATE FUNCTION 的其他缺失属性(即 EXECUTE AS、RETURNS NULL ON NULL INPUT 等)，但要使其在 SSDT 发布过程中尽可能无缝地工作有点棘手，因此没有预计何时完成.完成后，我将使用下载链接更新此答案。

更新:
最后，手动处理 T-SQL 包装器对象创建可能是最简单的方法。您可以让 Visual Studio 生成程序集和任何必要的数据库设置语句，然后自己执行 CREATE PROCEDURE、CREATE FUNCTION 等操作:

1. 在项目属性 -> 项目设置中选中创建脚本(.sql 文件)复选框。
2. 在 Project Properties -> SQLCLR 中取消选中 Generate DDL 复选框。
3. 添加新项 -> SQL Server -> 用户脚本 -> 部署后脚本 并在此脚本中添加您的 CREATE 语句。