Cannot insert the value NULL into column 'Reason', table 'dbo.History'; column does not allow nulls. INSERT fails

I am modifying a stored procedure to track who made changes.

The modifications are going into the field @summary varchar(max).

ALTER Procedure [dbo].[spVndr_UpdateRecord]
(   @nVID       Int = Null, 
    @sVendorName    Char(50) = Null, 
    @nVendorType    Int = Null, 
    @sAltName   Char(50) = Null, 
    @sTaxIDSSN  Char(20) = Null, 
    @sAddress   Char(30) = Null, 
    @sAddress2  Char(30) = Null, 
    @sCity      Char(20) = Null, 
    @sSt        Char(2) = Null, 
    @sZip       Char(10) = Null, 
    @sAreaCode  Char(10) = Null, 
    @sPhoneNum  Char(10) = Null, 
    @sExt       Char(3) = Null, 
    @sEmail     Char(50) = Null, 
    @sFax       Char(15) = Null, 
    @sOldID     Char(20) = Null, 
    @sLicenseNum    Char(20) = Null, 
    @sMedicareID    Char(20) = Null, 
    @sOtherID   Char(20) = Null, 
    @sMisc1     Char(20) = Null, 
    @sMisc2     Char(20) = Null, 
    @sMisc3     Char(20) = Null, 
    @sMisc4     Char(20) = Null, 
    @sWebPage   Char(50) = Null,
    @sComments  Char(255) = Null,
    @sEntryID   INT = Null,
    @sUserLogin varchar(100) = ''
) AS
DECLARE @VenTyp AS varchar(20), @oVendorName varchar(50), @oTaxIDSSN varchar(50),
    @oAddress varchar(50), @oCity varchar(50), @oSt varchar(50), @oZip varchar(50),
    @oAreaCode varchar(50), @oPhoneNum varchar(50), @oEmail varchar(50),
    @oLicenseNum varchar(50), @oMedicareID varchar(50), @oWebPage varchar(50);

SELECT @VenTyp = VendorType
FROM dbo.VendorType
WHERE idVendorType = @nVendorType   

UPDATE 
    dbo.Vendor
SET
    @oVendorName=RTRIM(ISNULL(VendorName, ' ')),
    VendorName = RTRIM(@sVendorName),
    VenTyp = @VenTyp,
    idVendorType = @nVendorType,
    AltName = RTRIM(@sAltName),
    @oTaxIDSSN=RTRIM(ISNULL(TaxIDSSN, ' ')),
    TaxIDSSN = RTRIM(@sTaxIDSSN),
    @oAddress=RTRIM(ISNULL(Address, ' ')),
    Address = RTRIM(@sAddress),
    Address2 = RTRIM(@sAddress2),
    @oCity=RTRIM(ISNULL(City, ' ')),
    City = RTRIM(@sCity),
    @oSt=RTRIM(ISNULL(St, ' ')),
    St = @sSt,
    @oZip=RTRIM(ISNULL(Zip, ' ')),
    Zip = @sZip,
    @oAreaCode=RTRIM(ISNULL(AreaCode, ' ')),
    AreaCode = @sAreaCode,
    @oPhoneNum=RTRIM(ISNULL(PhoneNum, ' ')),
    PhoneNum = @sPhoneNum,
    Ext = @sExt,
    @oEmail=RTRIM(ISNULL(Email, ' ')),
    Email = RTRIM(@sEmail),
    Fax = @sFax, 
    OldID = RTRIM(@sOldID),
    @oLicenseNum=RTRIM(ISNULL(LicenceNum, ' ')),
    LicenceNum = RTRIM(@sLicenseNum),
    @oMedicareID=RTRIM(ISNULL(MedicareID, ' ')),
    MedicareID = RTRIM(@sMedicareID),
    OtherID = RTRIM(@sOtherID),
    Misc1 = RTRIM(@sMisc1),
    Misc2 = RTRIM(@sMisc2),
    Misc3 = RTRIM(@sMisc3),
    Misc4 = RTRIM(@sMisc4),
    @oWebPage=RTRIM(ISNULL(WebPage, ' ')),
    WebPage = RTRIM(@sWebPage),
    Comments = RTRIM(@sComments),
    EntryID = CAST(@sEntryID AS CHAR(3))
WHERE idVendor = @nVID

DECLARE @summary varchar(max) = 
    'spVndr_UpdateRecord: ' +
    'Vendor(idVendor=[' + LTRIM(ISNULL(@nVID, ' ')) + ']) ' + 
    CASE WHEN @oVendorName<>@sVendorName
        THEN 'VendorName: (old: [' + @oVendorName + ']; new: [' + RTRIM(ISNULL(@sVendorName, ' ')) + ']); ' END +
    CASE WHEN @oTaxIDSSN<>@sTaxIDSSN
        THEN 'TaxIDSSN: (old: [' + RTRIM(@oTaxIDSSN) + ']; new: [' + RTRIM(ISNULL(@sTaxIDSSN, ' ')) + ']); ' END +
    CASE WHEN @oAddress<>@sAddress
        THEN 'Address: (old: [' + RTRIM(@oAddress) + ']; new: [' + RTRIM(ISNULL(@sAddress, ' ')) + ']); ' END +
    CASE WHEN @oCity<>@sCity
        THEN 'City: (old: [' + RTRIM(@oCity) + ']; new: [' + RTRIM(ISNULL(@sCity, ' ')) + ']); ' END +
    CASE WHEN @oSt<>@sSt
        THEN 'State: (old: [' + RTRIM(@oSt) + ']; new: [' + RTRIM(ISNULL(@sSt, ' ')) + ']); ' END +
    CASE WHEN @oZip<>@sZip
        THEN 'Zip: (old: [' + RTRIM(@oZip) + ']; new: [' + RTRIM(ISNULL(@sZip, ' ')) + ']); ' END +
    CASE WHEN @oAreaCode<>@sAreaCode
        THEN 'AreaCode: (old: [' + RTRIM(@oAreaCode) + ']; new: [' + RTRIM(ISNULL(@sAreaCode, ' ')) + ']); ' END +
    CASE WHEN @oPhoneNum<>@sPhoneNum
        THEN 'PhoneNum: (old: [' + RTRIM(@oPhoneNum) + ']; new: [' + RTRIM(ISNULL(@sPhoneNum, ' ')) + ']); ' END +
    CASE WHEN @oEmail<>@sEmail
        THEN 'Email: (old: [' + RTRIM(@oEmail) + ']; new: [' + RTRIM(ISNULL(@sEmail, ' ')) + ']); ' END +
    CASE WHEN @oLicenseNum<>@sLicenseNum
        THEN 'LicenseNum: (old: [' + RTRIM(@oLicenseNum) + ']; new: [' + RTRIM(ISNULL(@sLicenseNum, ' ')) + ']); ' END +
    CASE WHEN @oMedicareID<>@sMedicareID
        THEN 'MedicareID: (old: [' + RTRIM(@oMedicareID) + ']; new: [' + RTRIM(ISNULL(@sMedicareID, ' ')) + ']); ' END +
    CASE WHEN @oWebPage<>@sWebPage
        THEN 'WebPage: (old: [' + RTRIM(@oWebPage) + ']; new: [' + RTRIM(ISNULL(@sWebPage, ' ')) + ']); ' END +
    ' ';

-- Log event
INSERT INTO dbo.[History]
(
    [UserID],
    [Time],
    [Reason]
)
VALUES
(
    @sUserLogin,
    GETDATE(),
    @summary
)

It is long. I apologize, but the issue comes with the @summary value.

I am testing with various null values being passed in. When I build @summary, I convert all values to string.

Somewhere, what I wrote inserts a NULL value that causes the whole string to be null.

I keep getting this error, and I can’t see where it is coming from:

Cannot insert the value NULL into column ‘Reason’, table ‘dbo.History’; column does not allow nulls. INSERT fails.

I know if I try to append a NULL value to a string, it turns the whole string to NULL, but it looks like I covered every possible scenario.

Thanks in advance.

You need to sign in to view this answers