How to pass null to SQL Server stored procedure param with NON-NULL default value

Question

I have a SQL Server stored procedure inside a multi-tenant DB. It is used by all clients, but since I don't update them all at the same time I needed to provide default parameter values so clients wouldn't throw an error if they didn't pass in the parameter.

It looks like this:

ALTER PROCEDURE [dbo].[sp_UpdateSearchValues]  
    @pAuthID NVARCHAR(255),  
    @pgKey INT,   
    @pSearch01 NVARCHAR(255) = 'BTR:NOSEARCHUPDATE',  
    @pSearch02 NVARCHAR(255) = 'BTR:NOSEARCHUPDATE',  
    ..  
    @pSearch30 NVARCHAR(255) = 'BTR:NOSEARCHUPDATE'  

I couldn't use null as the default value because null is a valid value to pass in (to clear out a search index). However, when I assign a parameter to DBNull.Value, it seems that the stored procedure thinks nothing is passed and the stored procedure then uses the default 'BTR:NOSEARCHUPDATE' in its logic and does nothing (instead of clearing out the value) - if I assign DBNull, the != 'BTR;NOSEARCHUPDATE' below evaluates to false.

CASE WHEN @pSearch01 != 'BTR:NOSEARCHUPDATE' THEN @pSearch01 ELSE pSearch01 END,

This statement is simply trying to get the value of the parameter if 'something was passed in (DBNull or value)', otherwise fall back to the existing value in the the corresponding db field.

I assign DBNull using the following code:

UpdateCommand.Parameters[ "@pSearch19" ].Value = DBNull.Value;

So the question is, how can I pass in 'null' to the stored procedure so that it uses that as the value instead of simply using the default value 'BTR:NOSEARCHUPDATE'?

Answer 1

null is a valid value to pass, and it won't be overridden by the default parameter value. I can't repro this behavior in T-SQL or ADO.NET.

EG for

create or alter procedure [dbo].[sp_UpdateSearchValues]  
    @pAuthID nvarchar(255),  
    @pgKey int,   
    @pSearch01 nvarchar(255) = 'BTR:NOSEARCHUPDATE',  
    @pSearch02 nvarchar(255) = 'BTR:NOSEARCHUPDATE',  
    @pSearch30 nvarchar(255) = 'BTR:NOSEARCHUPDATE' 
as
begin
    select @pSearch01 search01
end

then in .NET

using (var con = new SqlConnection("server=.;database=tempdb;integrated security=true"))
{
   con.Open();

   SqlCommand cmd = con.CreateCommand();
   cmd.CommandText = "sp_UpdateSearchValues";
   cmd.CommandType = System.Data.CommandType.StoredProcedure;

   var pAuthID = cmd.Parameters.Add("@pAuthID", SqlDbType.NVarChar,255 );
   var pgKey = cmd.Parameters.Add("@pgKey", SqlDbType.Int);
   var pSearch01 = cmd.Parameters.Add("@pSearch01", SqlDbType.NVarChar, 255);

   pAuthID.Value = "a";
   pgKey.Value = 1;
   pSearch01.Value = DBNull.Value;

   var r = cmd.ExecuteScalar();

   Console.WriteLine($"{r} {r.GetType().Name}");

   Console.ReadKey();
}

Outputs

DBNull

But it looks like this is a simple case of null comparison and 3-valued logic.

Consider:

declare @pSearch01 nvarchar(200) = null
select CASE WHEN @pSearch01 != 'BTR:NOSEARCHUPDATE' then 1 else 0 end

What does that return? Is null != 'BTR:NOSEARCHUPDATE' a true statement? No it's not.