如何在ADO.NET中调用表值函数时传递默认关键字

Question

这里有个问题。在我们的数据库中，出于安全性和可维护性的考虑，我们将大部分读操作（即 select 语句）都封装在表值函数中。所以我有一个定义了一个或多个可选参数的 TVF。

我认为，当调用具有默认参数的 TVF 时，需要使用关键字 default，例如：

select * from fn_SampleTVF(123, DEFAULT, DEFAULT)

没问题，查询分析器里一切正常。但是当我们从ADO.NET实际发起请求时，我不确定如何创建一个SQL参数，以便将单词default实际放入渲染的SQL中。

目前我的大致代码如下：

String qry = "select * from fn_SampleTVF(@requiredParam, @optionalParam)";

DbCommand command = this.CreateStoreCommand(qry, CommandType.Text);

SqlParameter someRequiredParam = new SqlParameter("@requiredParam", SqlDbType.Int);
someRequiredParam.Value = 123;
command.Parameters.Add(someRequiredParam);

SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int);
optionalParam.Value = >>>> WTF? <<<<
command.Parameters.Add(optionalParam);

那么，有人有什么想法如何将default传递给TVF吗？

- Mason

---

到目前为止，我看到的最好的解决方案是避免在TVF参数定义中使用默认子句，像Kevin建议的那样传递null，并在TVF主体中手动创建默认值。这是一篇我找到的文章（它是关于Sprocs的，但问题是相同的）。http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/3825fe1c-7b7e-4642-826b-ea024804f807与微软相关的所有其他事情一样，使其正常工作将涉及一个半成品的解决方法。 - Mason

Answer 1

SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int);
optionalParam.Value = >>>> WTF? <<<<
command.Parameters.Add(optionalParam);

对于默认值，您无需添加上述代码（可选参数）。 SQL Server将使用您UDF中定义的默认值。 但是，如果您想传递不同的值，则可以传递：

SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int); 
optionalParam.Value = newValue; 
command.Parameters.Add(optionalParam); 

- Mohamed Aslam

---

此外，从我目前的测试来看，甚至不必指定数据类型；因此，您可以这样说： command.Parameters.Add(new SqlParameter("@OptionalParam", null));
...而 [null] 将被解释为 [默认值]。 - tbone

编辑：我的先前评论是不正确的：[null] 不被解释为 [default]。 - tbone

好的，经过更多的测试，至少在我使用的当前框架版本中，添加参数而不指定值并不会导致使用默认值。即使这是暗示的行为，但使用[default]从SSMS调用并不产生与不传递值的参数相同的结果：https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.value%28v=vs.110%29.aspx - tbone

换句话说，我认为这个答案（“您不必为默认情况添加上述代码（可选参数）。”）是不正确的，或者至少并非总是如此。 - tbone

Answer 2

我本来会这样做的：

public void YourMethod(int rparam, int? oparam = null)
{
    String qry = string.Format("select * from fn_SampleTVF(@requiredParam, {0})"
        , !oparam.HasValue ? "default" : "@optionalParam");

    SqlParameter someRequiredParam = new SqlParameter("@requiredParam", SqlDbType.Int);
    someRequiredParam.Value = rparam;
    command.Parameters.Add(someRequiredParam);

    if (oparam.HasValue)
    {
        SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int);
        optionalParam.Value = oparam.Value;
        command.Parameters.Add(optionalParam);
    }
}

- Andrey Morozov

Answer 3

你可以将Null作为参数值传递。

这篇文章展示了一些例子。

- Kevin Buchan

---

谢谢，我会尝试一下，但我相当确定传递一个显式的 null 值只会覆盖在 tvf 定义中定义的默认值，使得这些默认值变得无用。 - Mason

我很想听听你的发现。看到你的问题之前，我很惊讶自己以前从未需要了解这个。我仍然期望 Null 能够正常工作，并且 DBNull 将覆盖参数的定义。 - Kevin Buchan

是的，我刚刚检查了一下，那样不好。原因是因为以下两者之间存在差异：select * from fn_SampleTVF(123, DEFAULT)和select * from fn_SampleTVF(123, null)不过还是谢谢你。 - Mason

问题是你说得对。 DBNull确实覆盖了TVF的参数定义。我想要做的是使用TVF的参数定义。 - Mason