Default value for sql variable when DBNull.Value passed from code

Question

I'm using a service that is used between multiple projects. In this service we do the calls to insert/update sql tables using stored procedures. From the project if a variable is empty or null the application should pass through a db null value and in turn sql should set the default value to 'N'. This, however is throwing an exception that a null value cannot be inserted.

Assigning a value to a paramater:

     cmd.Parameters.AddWithValue("@PP_Ref", IIf(String.IsNullOrEmpty(ParkingPermit), DBNull.Value, ParkingPermit))

This is how the variable is set in the sql stored procedure:

    ALTER PROCEDURE [dbo].[SP_SaveCustomer]
          @PP_Ref           char(1) = 'N',

Exception that is received:

     {"Cannot insert the value NULL into column 'Cust_PP_Ind', table 'dbo.Customer'; 
      column does not allow nulls. INSERT fails." & vbCrLf & "The statement has been terminated."}

The variable passed will populate 'Cust_PP_Ind' (Cust_PP_Ind = @PP_Ref) in the insert and update statement. How do I set the default value through sql and not through code (and allow null value to be passed from code)?

GarethD · Accepted Answer · 2020-06-08 13:29:55Z

2

That isn't how default parameter values work unfortunately. The default value applies when the parameter isn't passed, not when the parameter is null. You have two choices (that I can see). The first would be to not pass the parameter if it is empty and keep the stored procedure as it is (I have replaced AddWithValue with Add because it is much better practise:

if (!String.IsNullOrEmpty(ParkingPermit))
{
    cmd.Parameters.Add("@PP_Ref", SqlDbType.Char, 1).Value = ParkingPermit;
}

The second would be to set the defaults inside the body of your procedure and leave the calling code as it is:

ALTER PROCEDURE [dbo].[SP_SaveCustomer] @PP_Ref CHAR(1) = NULL
AS
BEGIN

    SET @PP_Ref = ISNULL(NULLIF(@PPRef, ''), 'N');
    --REST OF THE PROCEDURE
END

edited Jun 8, 2020 at 13:29

answered Jun 8, 2020 at 13:18

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Default value for sql variable when DBNull.Value passed from code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related