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 a PK table.

A 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 a stored proc passes NULL for one (or more) parameters, or if the caller has not passed anything for one (or more) parameters?

C # call example:

Caller points to 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 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); } 

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

I assume that I can specify default values ​​that can safely be assumed that the caller will never use - something like this:

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

Then in the saved proc I can check the values ​​of undefined - if the vars parameter is still set to NameIsUndefined , EmailIsUndefined and / or PhoneIsUndefined , then I can safely assume that the caller did not explicitly specify values ​​for these parameters. Is this the only way to achieve my goal?

Answer 1

Unable to tell the difference between NULL and NULL in SQL Server, AFAIK.

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

Answer 2

If you declare your parameters like this (without a default value), the stored proc will require all four of them and it will simply fail if any of them are not passed to the supplier's EXEC statement.

You can declare some optional parameters as follows:

@Phone nvarchar(20) = NULL

however, it will not be possible to tell sproc inside if it was omitted or explicitly set to NULL .