如何确定参数值是否传递给存储过程

Question

我希望创建一个存储过程（在SQL Server 2008 R2中），该存储过程将根据表的主键更新表中的记录。

例如，存储过程将具有四个参数：

@ID int,
@Name nvarchar(50),
@Email nvarchar(80),
@Phone nvarchar(20)

我如何确定存储过程的调用者是否为一个或多个参数传递了NULL值，还是调用者没有为一个或多个参数传递任何内容？

C# 调用者示例：

调用者为 @Phone 指定了 NULL 值：

using (SqlCommand cmd = new SqlCommand())
{
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.CommandText = "EditPerson";
  cmd.Parameters.AddWithValue("@ID", id);
  cmd.Parameters.AddWithValue("@Name", 'Frank');
  cmd.Parameters.AddWithValue("@Email", '[e-mail]');
  cmd.Parameters.AddWithValue("@Phone", DBNull.Value);
  DatabaseManager.instance.ExecuteScalarQuery(cmd);
}

调用者忽略了@Phone参数：

using (SqlCommand cmd = new SqlCommand())
{
   cmd.CommandType = System.Data.CommandType.StoredProcedure;
   cmd.CommandText = "EditPerson";
   cmd.Parameters.AddWithValue("@ID", id);
   cmd.Parameters.AddWithValue("@Name", 'Frank');
   cmd.Parameters.AddWithValue("@Email", '[e-mail]');
   DatabaseManager.instance.ExecuteScalarQuery(cmd);
}

我在这里努力的目标是: 当调用者明确指定参数的值为NULL时，我将使用NULL值更新记录。然而，如果用户显式忽略传递参数，则UPDATE查询将保留特定记录已经设定的字段/列的值(即查询不会更新该特定列)。

我想我可以指定默认值，可以安全地假定调用者永远不会使用 - 就像这样：

@ID int,
@Name nvarchar(50) = 'NameIsUndefined',
@Email nvarchar(80) = 'EmailIsUndefined',
@Phone nvarchar(20) = 'PhoneIsUndefined'

然后，在存储过程中，我可以检查未定义的值——如果参数变量仍设置为NameIsUndefined，EmailIsUndefined和/或PhoneIsUndefined值，则我可以安全地假设调用方未显式定义这些参数的值。这是实现我的目标的唯一方法吗？

- Jed

---

是的，你需要使用一个非 NULL 的值来表示不同的状态。因此，要么设置一个默认值而非 NULL，要么传入一个非 NULL 的值。选择使用 NULL 或其他值来表示未传递任何值。 - nekno

Answer 1

如果您像这样声明参数（没有默认值），则存储过程将需要所有四个参数，如果其中任何一个未传递到由提供程序组成的EXEC语句中，则会失败。

您可以像这样声明一些参数是可选的： @Phone nvarchar(20) = NULL 但是，在存储过程内部无法确定是省略了它还是显式设置为NULL。

- Quassnoi

Answer 2

我所知道的是，在SQL Server中无法区分NULL和NULL之间的区别。

在您的C#代码中，我建议 A) 传递另一个值（比如空字符串）来表示传递了空值，然后在SQL中处理以将NULL写入数据库，如果传递了值，则保留先前的值，如果变量为NULL，则保留之前的值; 或者B）不要传递DBNull.Value，而是传递从数据库中读取的先前的值。

- nekno