sql server - Cannot insert the value NULL into column '', table column does not allow nulls. INSERT fails

Question

Code:

ALTER PROCEDURE [dbo].[SP_LMS_dealerorusercreation_IUDS]
@dealrid bigint,
@rid bigint,
@stateid bigint,
@regonid bigint,
@Locid bigint,
@pid varchar(MAX),
@address varchar(max),
@dealrname varchar(25),
@landno bigint,
@mobno bigint,
@altcontno bigint,
@email varchar(35),
@desig varchar(25),
@reporting varchar(30),
@status int,
@action varchar(10),
@CompanyId Uniqueidentifier

AS
DECLARE @TranStatus VARCHAR(5)
 BEGIN TRY
    BEGIN TRANSACTION
      IF(@action='Insert')
         BEGIN
          INSERT INTO LMS_dealerorusercreation(
rid,
stateid,
regonid,
Locid,
addres,
dealrname,
landno,
mobno,
altcontno,
email,
desig,
reporting,
status,
CompanyId
)
VALUES(
@rid,
@stateid,
@regonid,
@Locid,
@address,
@dealrname,
@landno,
@mobno,
@altcontno,
@email,
@desig,
@reporting,
@status,
@CompanyId
)
SELECT @dealrid = dealrid FROM LMS_dealerorusercreation WHERE mobno = @mobno AND email = @email
EXEC [dbo].[SP_LMS_SetDealerProductMapping]
@dealerId = @dealrid,
@prodid = @pid
SET @TranStatus='TRUE';
END       

IF(@action='Update')
     BEGIN
    UPDATE LMS_dealerorusercreation set rid= @rid,
    stateid=@stateid,
    regonid=@regonid,
    Locid=@Locid,
    addres=@address,
    dealrname=@dealrname,
    landno=@landno,
    mobno=@mobno,
    altcontno=@altcontno,
    email=@email,
    desig=@desig,
    reporting=@reporting,
    status=@status
    WHERE dealrid=@dealrid

SET @TranStatus='TRUE';       
    END              
    IF(@action='Delete')
    BEGIN
    DELETE FROM LMS_dealerorusercreation WHERE dealrid=@dealrid
    SET @TranStatus='TRUE';
    END
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION
    DECLARE @AI VARCHAR(MAX)
    DECLARE @EM VARCHAR(MAX);
    SET @AI = 'Not Provided'
    SET @EM = ERROR_MESSAGE();
    EXEC USP_SetException 
    @ExceptionDetail = @EM,
    @AdditionalInfo = @AI
    SET @TranStatus='FALSE';
    END CATCH
    SELECT @TranStatus;

The error am getting is

Cannot insert the value NULL into column 'dealrid', table 'DB_LMS.dbo.LMS_dealerorusercreation'; column does not allow nulls. INSERT fails.

Answer 1

You need to do one of two things, either...

• ensure that you pass in a non-null value for the column, or;
• ensure that your column accepts a null value if that is a desired property of the field.