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'?

ado.net will use the default when a vb.net 'Nothing' or c# 'Null' object are passed through. DBNull.Value should be passing in an actual DBNull (highlighted in @David Browne's answer. Is there anything else happening in your code before you execute your statement that could be changing the value of the parameter? — Mathew Paxinos, Feb 07 '19 at 02:06
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Feb 07 '19 at 05:49
@marc_s Yes I know about the sp_ prefix. But the code was written (incorrectly) 15 years ago and I'm not able to change those SP names. :( — Terry, Feb 07 '19 at 13:23

score 1 · Accepted Answer · edited Feb 12 '19 at 21:10

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.

I updated my question a bit showing the logic in the stored procedure. *And*, I had my statement reversed on the problem I think :|. What I meant to say is that when you pass in DBNull.Value, the stored procedure thinks that it should use the 'default' parameter value. You can see the stored procedure logic in the updated question. Is there a way to indicate that a parameter has been 'filled in'? — Terry, Feb 07 '19 at 22:50
Ah, so I need something like `SELECT CASE WHEN ( @pSearch01 IS NULL or @pSearch01 != 'BTR:NOSEARCHUPDATE' ) THEN @pSearch01 ELSE 'ValueFromDB' END search01`? — Terry, Feb 07 '19 at 22:59

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

1 Answers1