Passing Nullable Integer (int? SomeValue = NULL) As Parameter To Stored Procedure

Question

I'm trying to create a stored procedure that would take 4 paramters. These 4 paramters dictate how the returned table will look like.

@C_ID parameter is always numeric. @U_ID and @P_ID values can contain a valid numeric value, or can be (or should be) passed as NULL so WHERE conditions either execute or not.

ALTER PROCEDURE [dbo].[GetData]
    @C_ID integer,
    @U_ID integer = Null,
    @P_ID integer = Null,
    @SortIndex integer = 1
AS 
BEGIN
    SELECT
        ID, P_ID, U_Name, P_Name, 
        FORMAT(Date_When, 'dd/MM/yyyy hh:mm tt')
    FROM 
        SomeTable
    WHERE 
        C_ID = @C_ID 
        AND (@P_ID IS NULL OR P_ID = @P_ID) 
        AND (@U_ID IS NULL OR U_ID = @U_ID)
   ORDER BY 
        CASE WHEN @SortIndex = 1 THEN -ID 
             ELSE ID 
        END ASC
END

On SQL Server 2014 the following executions work fine without any errors:

exec GetData '15', null, null, '1';
exec GetData '15', '1', null, '1';
exec GetData '15', null, '1', '1';
exec GetData '15', '1', '1', '1';
...

However on C# side the following code fails to execute:

int? SomeValue = null;
Adapter = new SqlDataAdapter("exec GetData '15'," + SomeValue + ",null,'1';", Connection);
Adapter.Fill(Data);

which gives me an error

Incorrect syntax near ','.

If I change the SomeValue variable to 1, it works perfectly fine.

DBNull.Value and simply leaving the parameter as ' ' do not work either.

So the question would be: how would I (if it is possible) be able to pass nullable integer to SQL given that it can be both null and a valid number?

[Please do not construct SQL queries with concatenation](http://stackoverflow.com/q/332365/11683). — GSerg, Jan 31 '16 at 15:16
As for the formal answer to your question, this is because `SomeValue `, being `null`, inserts an empty string into the resulting string, which results in two subsequent commas. — GSerg, Jan 31 '16 at 15:18

Marco · Accepted Answer · 2016-01-31T16:30:10.207

7

Do something along those lines:

Adapter = new SqlDataAdapter();

//con is an open SQLConnection
var cmd = new SQLCommand("GetData", con);
cmd.Parameters.Add(new SqlParameter("@C_ID", 15));
cmd.Parameters.Add(new SqlParameter("@U_ID", SomeValue ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@P_ID", SomeValue ?? DBNull.Value));
cmd.Parameters.Add(new SqlParameter("@SortIndex", 1));
cmd.CommandType = CommandType.StoredProcedure;
Adapter.SelectCommand = cmd;
Adapter.Fill(Data);

The ?? is called the null-coalescing operator and checks if SomeValue is null. If so, the value behind the ?? is used - in your case DBNull.Value.

And as GSerg already pointed out, always keep Little Bobby Tables in the back of your head.

Edit: Null-coalescing reference

edited Jan 31 '16 at 16:30

answered Jan 31 '16 at 15:22

Marco

22,856
9
75
124

Thank you very much, I don't think I've encountered ?? operator before. – OverflowStack Jan 31 '16 at 15:28
1

Operator '??' cannot be applied to operands of type 'int?' and 'System.DBNull' If I change it to SomeValue ?? (object)DBNull.Value I get : ExecuteReader: CommandText property has not been initialized – OverflowStack Jan 31 '16 at 15:43
Oh, I forgot to pass a valid connection into the SQLCommand. You need to do this: `var cmd = new SQLCommand("GetData", con);` where `con` is an open `SQLConnection`. This plus casting DBNull.Value to object, like you already did, should do the trick. – Marco Jan 31 '16 at 16:29
It wont work until you cast integer to object. ("@CategoryID", (object)id ?? DBNull.Value) – erhan355 Oct 25 '18 at 13:50

score 1 · Answer 2 · answered Jan 31 '16 at 15:19

1

Did you tried something like below?

SomeValue ?? "null"

answered Jan 31 '16 at 15:19

mano

136
3

Passing Nullable Integer (int? SomeValue = NULL) As Parameter To Stored Procedure

2 Answers2