Getting a SqlException when I try to insert null values through a stored procedure

Question

I have a table that accepts a bunch of parameters and saves it as a new contact through a stored procedure using an INSERT INTO statement. For some reason I get a SqlException when some of my parameters are left null. All of the parameters left null are indeed nullable in the SQL Server table, so I don't understand the issue. My thoughts are that my INSERT statement is accepting all the parameters even if they are null and trying to insert them into my table, which I believe is a syntactical "no-no"

Anyway, here is the C# code:

try
    {
        using (SqlConnection sqlConn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("Insert_NewContact", sqlConn))
            {
                sqlConn.Open();

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@CompanyID", CompanyID);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@phone", phone);
                cmd.Parameters.AddWithValue("@fax", fax);
                cmd.Parameters.AddWithValue("@fName", fName);
                cmd.Parameters.AddWithValue("@lName", lName);
                cmd.Parameters.AddWithValue("@sendVia", sendVia);                   
                cmd.Parameters.AddWithValue("@default", defaultContact);
                cmd.Parameters.AddWithValue("@repo", repo);
                cmd.Parameters.AddWithValue("@fail", fail);
                cmd.Parameters.AddWithValue("@borrow", borrow);
                cmd.Parameters.AddWithValue("@loan", loan);

                cmd.ExecuteNonQuery();

                sqlConn.Close();
            }
        }
    }
    catch (Exception e)
    {
        throw e;
    }

and here is the stored procedure in SQL:

ALTER PROCEDURE [dbo].[Insert_NewContact]

@CompanyID  INT,
@email      VARCHAR(50),
@phone      VARCHAR(50),
@fax        VARCHAR(50),
@fName      VARCHAR(50),
@lName      VARCHAR(50),
@sendVia    VARCHAR(50),
@default    BIT,
@repo       TINYINT,
@fail       TINYINT,
@borrow     TINYINT,
@loan       TINYINT

AS
 BEGIN
    SET NOCOUNT ON;

BEGIN TRY

    INSERT INTO Master_Contacts(
                                    companyID, 
                                    fName, 
                                    lName, 
                                    phone, 
                                    email, 
                                    fax, 
                                    send_via, 
                                    defaultcontact, 
                                    repoRole, 
                                    borrowRole, 
                                    failRole, 
                                    loanRole
                                )
    VALUES                      (
                                    @CompanyID,
                                    @fName,
                                    @lName,
                                    @phone,
                                    @email,
                                    @fax,
                                    @sendVia,
                                    @default,
                                    @repo,
                                    @borrow,
                                    @fail,
                                    @loan
                                )                   
END TRY

Not sure why the AS BEGIN and NOCOUNT are so weird, but they are correct in the Stored Proc.

Anyway, if I leave the email, phone, fax, etc. empty in my application, I get this error:

SqlException was unhandled by user code Procedure or function 'Insert_NewContact' expects parameter '@email', which was not supplied.

How can I edit my stored proc to make it work with null values?

Answer 1

You should pass DBNull.Value when you have a null value. For example.

 cmd.Parameters.AddWithValue("@email", email == null ? DBNull.Value : (object)email);

Answer 2

ALTER PROCEDURE [dbo].[Insert_NewContact]

@CompanyID  INT,
@email      VARCHAR(50) = null,
@phone      VARCHAR(50) = null,
@fax        VARCHAR(50) = null,
@fName      VARCHAR(50) = null,
@lName      VARCHAR(50) = null,
@sendVia    VARCHAR(50) = null,
@default    BIT = 0,
@repo       TINYINT = 0,
@fail       TINYINT = 0,
@borrow     TINYINT = 0,
@loan       TINYINT = 0

AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY

INSERT INTO Master_Contacts(
                                companyID, 
                                fName, 
                                lName, 
                                phone, 
                                email, 
                                fax, 
                                send_via, 
                                defaultcontact, 
                                repoRole, 
                                borrowRole, 
                                failRole, 
                                loanRole
                            )
VALUES                      (
                                @CompanyID,
                                @fName,
                                @lName,
                                @phone,
                                @email,
                                @fax,
                                @sendVia,
                                @default,
                                @repo,
                                @borrow,
                                @fail,
                                @loan
                            )                   
END TRY

Answer 3

Set the default value for the parameter as null, try this:

@CompanyID  INT,
@email      VARCHAR(50) = null,
@phone      VARCHAR(50)  = null,
@fax        VARCHAR(50)  = null,
@fName      VARCHAR(50) = null,
@lName      VARCHAR(50),
@sendVia    VARCHAR(50),
@default    BIT,
@repo       TINYINT,
@fail       TINYINT,
@borrow     TINYINT,
@loan       TINYINT