Send a empty DateTime value into SQL stored procedure. But not null

Question

I have an SQL stored procedure which accepts a DateTime parameter which has a default value of NULL

@pmNext_Check_Date DATETIME=NULL

I want to use this parameter in 3 scenarios:

1. If it's NULL then don't update any records
2. If it's got a date value then update all my records specified in my WHERE clause
3. The problem one! Set all the date fields in my query to NULL for the records in my WHERE clause.

Here is the block of code within the SP that is causing me issues (the rest of the UPDATE statement is build elsewhere in the SP and works fine):

IF @pmNext_Check_Date IS NOT NULL
    IF @pmNext_Check_Date ='' --This is the bit that is causing me a problem. I just need to check for a empty date
        SET @sql = @sql + ' Next_Check_Date = NULL '
    ELSE
        SET @sql = @sql + ' Next_Check_Date = @pmNext_Check_Date '                    

SET @sql = @sql + ' WHERE ID IN (1, 2)'

So for example if I have the following 2 rows:

ID NextCheckDate

1 12/12/12

2 NULL

In scenario 1 I wouldn't pass the parameter in as the procedure will use the default value and no dates will be updated.

In scenario 2 I pass in a date value and update both rows with the date value

In scenario 3 I want to update the date value on my rows to be null. The difference between scenario 1 & 3 is in scenario 3 the user will be choosing to set the date values to null.

So, I wanted to pass a blank date into the stored procedure. I'm doing this from C# and would like to do something like the following:

SqlParameter param = new SqlParameter("@pmNext_Check_Date", "");                            

This fails as the SP is expecting a DateTime.

So I want to be able to pass in a blank date and also how do I check this within the SP. The current check which is below doesn't work:

IF @pmNext_Check_Date =''

Thanks in advance. Hope this all makes sense. I'm using C#4.0 and SQL 2008

Answer 1

There is no such thing as a "blank date". You could use a well-known sentinel value (01 Jan for some arbitrary ancient year, for example), but null would be preferable. Note that to pass an explicit null via a parameter, you need:

SqlParameter param = new SqlParameter("@pmNext_Check_Date", DBNull.Value);

If that doesn't have enough granularity, consider adding a separate boolean (bit) parameter (or similar) that clarifies what you want the sproc to do. Or: have multiple procs to do these different things.

One useful sentinel value for SQL Server is January 1, 1753 (SQL Server's minimum datetime value) - this can be produced in TSQL without string parsing as cast(-53690 as datetime).