Not null-able integer becomes null when passed to stored procedure

Question

I see some very strange behaviour when I pass integer from .NET code to SQL.

.NET

int facilityID = 0;
//...
var param = new SqlParameter("@facid", facilityID)
// db call
using (SqlConnection connectionToSQL = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(storedProcedure, connectionToSQL))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandTimeout = commandTimeout;

        command.Parameters.Add(param)

        adapter = new SqlDataAdapter(command);

        connectionToSQL.Open();

        data = new DataSet();
        adapter.Fill(data);
    }
}

SQL

CREATE PROCEDURE MySP @facid int = NULL, 
-- ...
SELECT
-- ...
WHERE       r.entity = isnull(@facid,r.entity) -- r.entity defined as [entity] [numeric] (18, 0) NULL, however it contains only numbers > 0 (no NULLs)

So this select statement can return data only if @facid is NULL or some number > 0 that matches to one of r.entity values, HOWEVER when I run .NET code it returns results. Can someone please explain how this is possible? What exactly and why it makes @facid=NULL despite that I pass 0 from .NET code.

.NET 4.5

System.Data version 4.0.0.0

Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

You never passed any value to that parameter. You passed `0` as the SqlDbType. to [SqlParameter(String.SqlDbType)](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter.-ctor?view=netframework-4.7.2#System_Data_SqlClient_SqlParameter__ctor_System_String_System_Data_SqlDbType_). Enums are actually numbers so the best overload for the compiler to use is the one that accepts a `SqlDbType`, not the one that accepts an object — Panagiotis Kanavos, Feb 13 '19 at 11:59
That constructor is used very rarely. What you encountered is one reason. Another one is that the class *guesses* what the type is from the value, which means it can get the type or size wrong. `AddWithValues` has the exact same problem. That's why tutorials use `Add` or one of the *other* constructors — Panagiotis Kanavos, Feb 13 '19 at 12:02
@PanagiotisKanavos nope, I am using SqlParameter(String, Object) constructor, Enums are numbers you right, however you need to cast it e.g. new SqlParameter("@facid", (SqlDbType)facilityID) — Vladimirs, Feb 13 '19 at 12:27
You are *not*, unless you explicitly cast the integer to an object. That's because that enum is a better match than an `object`. You need to box the integer to get an object, it's not just a cast. In any case, don't use that constructor. Apart from that, it will fail if you pass a `null` (what's a null's type?), if you pass a string that's too long or short to a fixed-width `char` parameter and more — Panagiotis Kanavos, Feb 13 '19 at 12:29
@PanagiotisKanavos I am not sure if it actually guesses, I would expect this constructor to call GetType or something in order to determine type. I think you might be right that it is something to do with this constructor, however I am still not sure what and why exactly is happening. — Vladimirs, Feb 13 '19 at 12:30
That constructor *doesn't* get called at all because the *compiler* sees that the best choice is the one that accepts an integer - that's the one that accepts SqlDbType. — Panagiotis Kanavos, Feb 13 '19 at 12:31
The [constructor's docs](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter.-ctor?redirectedfrom=MSDN&view=netframework-4.7.2#System_Data_SqlClient_SqlParameter__ctor_System_String_System_Object_) warn against that exact problem: `If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameter(String, SqlDbType) constructor overload.` — Panagiotis Kanavos, Feb 13 '19 at 12:34

Not null-able integer becomes null when passed to stored procedure

0 Answers0