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
– Mathew Paxinos, Commented Feb 7, 2019 at 2: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), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Feb 7, 2019 at 5: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
– Terry, Commented Feb 7, 2019 at 13:23

Brian · Accepted Answer · 2019-02-12 21:10:57Z

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.

edited Feb 12, 2019 at 21:10

Brian

5,1377 gold badges41 silver badges52 bronze badges

answered Feb 6, 2019 at 23:47

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Terry Over a year ago

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 Over a year ago

Ah, so I need something like

SELECT CASE WHEN ( @pSearch01 IS NULL or @pSearch01 != 'BTR:NOSEARCHUPDATE' ) THEN @pSearch01 ELSE 'ValueFromDB' END search01

?

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related