Passing Nullable Integer (int? SomeValue = NULL) As Parameter To Stored Procedure
<p>I'm trying to create a stored procedure that would take 4 paramters. These 4 paramters dictate how the returned table will look like.</p>

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

<pre><code>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
</code></pre>

<p>On SQL Server 2014 the following executions work fine without any errors:</p>

<pre><code>exec GetData '15', null, null, '1';
exec GetData '15', '1', null, '1';
exec GetData '15', null, '1', '1';
exec GetData '15', '1', '1', '1';
...
</code></pre>

<p>However on C# side the following code fails to execute:</p>

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

<p>which gives me an error </p>

<blockquote>
  <p>Incorrect syntax near ','.</p>
</blockquote>

<p>If I change the <code>SomeValue</code> variable to 1, it works perfectly fine.</p>

<p><code>DBNull.Value</code> and simply leaving the parameter as ' ' do not work either.</p>

<p>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?</p>