Although I send the parameter I get "Can not insert NULL value..."

Question

I am developing an ADO.NET application. At some point in the DAL I call a stored-procedure named "CREATE_CUSTOMER". Although I set the SHORT_NAME field I still get the

"Msg 515, Level 16, State 2, Procedure CREATE_CUSTOMER, Line 29
Cannot insert the value NULL into column 'SHORT_NAME', table 'MYDB.app.CUSTOMER';
column does not allow nulls. INSERT fails." error.

When I inspect the query with the SQL profiler I get the following SQL runs on the server. As I Copy&Paste it to a new Query Window I still get the same error.

Do I miss something?

declare @p16 int
set @p16=NULL

exec sp_executesql N'[app].[CREATE_CUSTOMER]',
N'@SHORT_NAME nvarchar(11),
@MAIL_NAME nvarchar(18),
@MT_SALESPERSON_ID int,
@CREDIT_LIMIT decimal(1,0),
@CREDIT_LIMIT_CURRENCY_ID int,
@PAYMENT_TYPE_ID int,
@SALES_TERM_ID int,
@FREE_STORAGE_DAY_ID int,
@RISK_GROUP_ID int,
@SECTOR_ID int,
@OCCUPATION_ID int,
@STORAGE_FEE_ID int,
@STATUS smallint,
@IDENTITY int output',

@SHORT_NAME=N'NEW Corp',
@MAIL_NAME=N'NEW Corporation',
@MT_SALESPERSON_ID=3,
@CREDIT_LIMIT=0,
@CREDIT_LIMIT_CURRENCY_ID=1,
@PAYMENT_TYPE_ID=4,
@SALES_TERM_ID=7,
@FREE_STORAGE_DAY_ID=6,
@RISK_GROUP_ID=3,
@SECTOR_ID=13,
@OCCUPATION_ID=16,
@STORAGE_FEE_ID=6,
@STATUS=0,
@IDENTITY=@p16 output

select @p16

And my Stored Procedure is as follows :

CREATE PROCEDURE [app].[CREATE_CUSTOMER]
    @SHORT_NAME varchar(250) = NULL,
    @MAIL_NAME varchar(500) = NULL,
    @MT_SALESPERSON_ID int = NULL,
    @CREDIT_LIMIT decimal(18,2) = NULL,
    @CREDIT_LIMIT_CURRENCY_ID int = NULL,
    @PAYMENT_TYPE_ID int = NULL,
    @SALES_TERM_ID int = NULL,
    @FREE_STORAGE_DAY_ID int = NULL,
    @RISK_GROUP_ID int = NULL,
    @SECTOR_ID int = NULL,
    @OCCUPATION_ID int = NULL,
    @STORAGE_FEE_ID int = NULL,
    @STATUS tinyint = NULL,

    @IDENTITY INT = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [app].[CUSTOMER]
       ([SHORT_NAME],
        [MAIL_NAME],
        [MT_SALESPERSON_ID],
        [CREDIT_LIMIT],
        [CREDIT_LIMIT_CURRENCY_ID],
        [PAYMENT_TYPE_ID],
        [SALES_TERM_ID],
        [FREE_STORAGE_DAY_ID],
        [RISK_GROUP_ID],
        [SECTOR_ID],
        [OCCUPATION_ID],
        [STORAGE_FEE_ID],
        [STATUS],
        [CREATE_DATE],
        [CREATE_USERID])
VALUES
       (@SHORT_NAME,
        @MAIL_NAME,
        @MT_SALESPERSON_ID,
        @CREDIT_LIMIT,
        @CREDIT_LIMIT_CURRENCY_ID,
        @PAYMENT_TYPE_ID,
        @SALES_TERM_ID,
        @FREE_STORAGE_DAY_ID,
        @RISK_GROUP_ID,
        @SECTOR_ID,
        @OCCUPATION_ID,
        @STORAGE_FEE_ID,
        @STATUS,
        GETDATE(),
        CONTEXT_INFO())

SELECT @IDENTITY = SCOPE_IDENTITY()
END

This SQL code is being generated by the ADO.NET. Actual C# code is :

    private static ICustomer CreateCustomer(ICustomer customer, int contextUserId)
    {
        try
        {
            string sql = "[app].[CREATE_CUSTOMER]";
            SqlConnection conn = null;
            using (conn = GetConnection())
            {
                SetContextInfomationToConnection(conn, contextUserId);
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@SHORT_NAME", customer.ShortName);
                cmd.Parameters.AddWithValue("@MAIL_NAME", customer.MailName);
                cmd.Parameters.AddWithValue("@MT_SALESPERSON_ID", customer.SalesPersonId);
                cmd.Parameters.AddWithValue("@CREDIT_LIMIT", customer.CreditLimit);
                cmd.Parameters.AddWithValue("@CREDIT_LIMIT_CURRENCY_ID", customer.CreditLimitCurrencyId);
                cmd.Parameters.AddWithValue("@PAYMENT_TYPE_ID", customer.PaymentTypeId);
                cmd.Parameters.AddWithValue("@SALES_TERM_ID", customer.SalesTermId);
                cmd.Parameters.AddWithValue("@FREE_STORAGE_DAY_ID", customer.FreeStorageDayId);
                cmd.Parameters.AddWithValue("@RISK_GROUP_ID", customer.RiskGroupId);
                cmd.Parameters.AddWithValue("@SECTOR_ID", customer.SectorId);
                cmd.Parameters.AddWithValue("@OCCUPATION_ID", customer.OccupationId);
                cmd.Parameters.AddWithValue("@STORAGE_FEE_ID", customer.StorageFeeId);
                cmd.Parameters.AddWithValue("@STATUS", customer.Status);

                SqlParameter prmNewId = new SqlParameter("@IDENTITY", SqlDbType.Int, 4);
                prmNewId.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(prmNewId);

                cmd.ExecuteNonQuery();
                int id = prmNewId.Value != DBNull.Value ? (int)prmNewId.Value : -1;
                if (id > 0)
                {
                    customer.Id = id;
                    return customer;
                }
                else
                {
                    throw new Exception("Can not insert customer record with Id generation");
                }
            }
        }
        catch (Exception ex)
        {
            throw;
        }
    }

Answer 1

Do not assign null values to ur variable, try only with DECLARING it as bellow

DECLARE @SHORT_NAME varchar(250) ,

instead of

@SHORT_NAME varchar(250) = NULL,

Answer 2

Your code is the equivalent of doing this:

DECLARE @SHORT_NAME nvarchar(11) = N'NEW Corp';
...
EXEC [app].[CREATE_CUSTOMER];

You are just declaring the parameters, never actually passing them to the procedure invocation. Your code should be like this:

exec sp_executesql N'[app].[CREATE_CUSTOMER] @SHORT_NAME, @MAIL_NAME, ...',
   N'@SHORT_NAME nvarchar(11),
     @MAIL_NAME nvarchar(18),
     ...',
    @SHORT_NAME=N'NEW Corp',
    @MAIL_NAME=N'NEW Corporation',
    ...

You must not only declare the parameters you pass to the batch, you must also use them when you invoke the procedure.

When I inspect the query with the SQL profiler I get the following SQL runs on the server. As I Copy&Paste it to a new Query Window I still get the same error

This sounds suspiciously like you are using a SqlCommand but forgot to set the CommandType to Procedure. the default is Text and will behave exactly as you observed.