Is it safe to always type a DBNull SqlParameter as an int?

Question

Recently, we ran into the following issue:

• Null value in a parameter varbinary datatype

In a nutshell: myCommand.Parameters.AddWithValue("@SomeParameter", DBNull.Value); apparently "types" the DBNull parameter as an nvarchar, which can be implicitly converted to almost all other types, but, unfortunately, not varbinary, yielding the following error:

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Unfortunately, the solutions suggested in the linked question do not apply, since we use AddWithValue deep inside a data access library which is designed to infer the data type from the parameter type and does not support adding the "real" SQL Server type.

I "fixed" this issue by explicitly typing DBNull parameters as ints instead:

void MyImprovedAddWithValue(SqlCommand cmd, string key, object value)
{
    if (value == DBNull.Value) 
    {
        cmd.Parameters.Add(key, SqlDbType.Int).Value = DBNull.Value;
    }
    else
    {
        cmd.Parameters.AddWithValue(key, value);
    }
}

This seems to work. Apparently, NULL typed as int can be implicitly converted to any other type supported by SQL Server.

Ignoring the fact that AddWithValue has some well-known shortcomings (which we are perfectly aware of), are there any problems to be expected by using this approach? Did the designers of SqlParameterCollection.AddWithValue have a good reason not to do this "by default" which I am overlooking?

Answer 1

AddWithValue has more problems. There is the issue of query plan cache pollution caused by the fact that it creates a parameter of type NVARCHAR(X) where X is the exact length of the value. As NVARCHAR(73) is a different type from NVARCHAR(74), the SQL Server query plan cache gets polluted with many identical plans that differ only on the parameter type. Another issue is when a column of type VARCHAR is involved in a comparison and the parameter of type NVARCHAR causes type conversion than disqualifies use of an index on said column.

There are so many reasons against AddWithValue that I never use it, never recommend it, and always add typed wrappers around it. I suggest you do the same.

Answer 2

No, this is not always safe, per the conversion chart (under "implicit conversions"). INT can be implicitly converted to a lot, but not to DATE, TIME, DATETIMEOFFSET, DATETIME2, UNIQUEIDENTIFIER, IMAGE, NTEXT, TEXT, XML, CLR UDTs and HIERARCHYID. Some of these types are more common than others, but the incompatibility with DATETIME2 and UNIQUEIDENTIFIER would be easy to encounter in practice.

There are no T-SQL types that have implicit conversions to every other type, so there really is no good way of doing this if you don't know the target type. Of all the types, the character types CHAR and VARCHAR have the most implicit conversions, lacking only BINARY, VARBINARY (your case) and the far less used TIMESTAMP. NCHAR and NVARCHAR are close behind, additionally lacking the deprecated IMAGE type. With all this in mind, NVARCHAR is clearly the best choice for a default, since it can represent all strings and fails only on implicit conversion to the BINARY types, which are less common than the types INT does not convert to (even for NULL).