What causes @summary variable to fail in SQL Server stored procedure?

Question

I have about 15 existing stored procedures that I modified to add logging. All of them are working except this one.

When Visual Studio attempts to execute the query, it displays the following error:

System.Data.SqlClient.SqlException: 'Cannot insert the value NULL into column 'Reason', table 'Claims.dbo.History'; column does not allow nulls. INSERT fails.

In the SQL below, I do not see how the 'Reason' field could be null.

Microsoft SQL Server 2016 (SP3-GDR) (KB5046063) - 13.0.6450.1 (X64) Sep 27 2024 19:17:51 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

What have I missed?

USE [CCC]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spVendor_Active_Update] (
    @VendorID   int,
    @Active         bit,
    @sUserLogin     varchar(100) = ''
) AS

DECLARE @oldActive bit, @summary varchar(max);

SELECT @oldActive = v.IsActive
FROM CCC.dbo.Vendor v
WHERE v.idVendor = @VendorID

UPDATE  CCC.dbo.Vendor
SET
    IsActive =@Active
WHERE
    idVendor = @VendorID

SET @summary = 'CCC.dbo.spVendor_Active_Update: Vendor.IsActive (old): [' + Convert(varchar(10), @oldActive) + ']; (new): [' + Convert(varchar(10), @Active) + '];';

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

The record in 'Vendor' does get updated, but the INSERT into 'History' fails.

What have I missed?

I converted values for @oldActive and @Active to varchar(10), which should be more than plenty. A single 'char' should be fine, but I want to make sure.

Answer 1

Clearly somewhere there is a null value. Looking back through the code, either @Active was passed as null, or there was no row so @oldActive is null. Both of these conditions should have been handled properly. The former can be fixed with ISNULL, the latter you should just throw an error as the UPDATE never worked.

Also you can combine your SELECT and UPDATE into a single statement.

CREATE OR ALTER PROCEDURE dbo.spVendor_Active_Update (
    @VendorID   int,
    @Active         bit = 0,
    @sUserLogin     varchar(100) = ''
) AS

SET @Active = ISNULL(@Active, 0);

DECLARE @oldActive bit, @summary varchar(max);

UPDATE CCC.dbo.Vendor
SET
    @oldActive = IsActive
    IsActive   = @Active
WHERE
    idVendor = @VendorID;

IF @@ROWCOUNT = 0
    THROW 50001, N'No vendor found', 1;

SET @summary = 'CCC.dbo.spVendor_Active_Update: Vendor.IsActive (old): [' + Convert(varchar(10), @oldActive) + ']; (new): [' + Convert(varchar(10), @Active) + '];';

INSERT INTO Claims.dbo.History
(
    UserID,
    Time,
    Reason
)
VALUES
(
    @sUserLogin,
    GETDATE(),
    @summary
);

Answer 2

This was answered earlier today in the comments that are now missing:

SET @summary = 'CCC.dbo.spVendor_Active_Update: Vendor.IsActive (old): [' + Convert(varchar(10), ISNULL(@oldActive, 0)) + ']; (new): [' + Convert(varchar(10), ISNULL(@Active, 0)) + '];';

Adding ISNULL was necessary to convert null bit values to varchar.