How to determine if a parameter value was passed to a stored procedure

Question

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)

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 example:

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", '[email protected]');
  cmd.Parameters.AddWithValue("@Phone", DBNull.Value);
  DatabaseManager.instance.ExecuteScalarQuery(cmd);
}

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", '[email protected]');
   DatabaseManager.instance.ExecuteScalarQuery(cmd);
}

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'

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?

Answer 1

If you declare your parameters like this (without default value), the stored proc will require all four of them and will just fail if any of them will not be passed to the EXEC statement composed by the provider.

You may declare some of the parameters optional like this:

@Phone nvarchar(20) = NULL

however, there will be no way to tell inside the sproc if it was omitted or explicitly set to NULL.