如何确定参数值是否传递给存储过程 [英] How to determine if a parameter value was passed to a stored procedure

问题描述

我想创建一个存储过程(在 SQL Server 2008 R2 中)，它将根据表的 PK 更新表中的记录.

I want to create a stored procedure (in SQL Server 2008 R2) that will update a record in a table based on the table's PK.

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

The stored proc will have, for example, four parameters:

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

如何确定存储过程的调用者是否为一个(或多个)参数传递了 NULL 值，而调用者是否没有为一个(或多个)参数传递任何值?

How can I determine if the caller of the stored proc passes a NULL value for one (or more) of the parameters vs. if the caller didn't pass anything for one (or more) of the parameters?

C# 调用者示例:

Caller 为 @Phone 指定 NULL:

Caller specifies NULL for @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]');
  cmd.Parameters.AddWithValue("@Phone", DBNull.Value);
  DatabaseManager.instance.ExecuteScalarQuery(cmd);
}

Caller 忽略了 @Phone 参数:

Caller ignores the @Phone parameter:

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 查询将保留已为特定记录设置的字段/列的值(即查询不会更新该特定列).

What I'm trying to accomplish here is, if the caller explicitly specifies a NULL value for a parameter, then I will update the record with a NULL value. However, if the user explicitly ignores passing a parameter, then the UPDATE query will retain the value of the field/column that is already set for the particular record (i.e. the query will NOT update that particular column).

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

I suppose that I could specify default values that can be safely assumed that a caller will never use - something like this:

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

然后，在存储过程中，我可以检查未定义的值 - 如果参数变量仍然设置为 NameIsUndefined、EmailIsUndefined 和/或 PhoneIsUndefined 值，那么我可以安全地假设调用者没有明确定义这些参数的值.这是实现我的目标的唯一方法吗?

Then, in the stored proc, I can check for the undefined values - if the parameter vars are still set to the NameIsUndefined, EmailIsUndefined, and/or PhoneIsUndefined values, then I can safely assume that the caller did not explicitly define values for those params. Is this the only way to accomplish my goal?

推荐答案

没有办法区分 SQL Server 中的 NULL 和 NULL，AFAIK.

There's no way to tell the difference between NULL and NULL in SQL Server, AFAIK.

在您的 C# 代码中，我会 A) 传递另一个值，如空字符串，以指示传递了一个空值，然后在 SQL 中处理该值以在传递该值时将 NULL 写入数据库，或者保留以前的如果变量为 NULL，则为 value，或者 B) 不传递 DBNull.Value，而是传递从 DB 读取的先前值.

In your C# code, I would A) Pass another value, like an empty string, to indicate an empty value was passed, then process that in SQL to write NULL to the DB if the value was passed, or retain the previous value if the variable is NULL, or B) Instead of passing DBNull.Value, pass the previous value that was read from the DB.

这篇关于如何确定参数值是否传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！