SQL Error on Unnecessary varchar to int Conversion

Scenario

Consider the following table:

CREATE TABLE [dbo].[AuditTrail](

[AuditTrailID] [int] IDENTITY(1,1) NOT NULL,

[UserID] [int] NOT NULL,

[FieldName] [varchar](50) NULL,

[OldValue] [varchar](max) NULL,

[NewValue] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And consider the following script:

DECLARE @OldRoleID int

DECLARE @NewRoleID int

DECLARE @OldUsername varchar(25)

DECLARE @NewUsername varchar(25)

SET @OldRoleID = 1

SET @NewRoleID = 2

SET @OldUsername = 'Juan'

SET @NewUsername = 'John'

INSERT INTO dbo.AuditTrail VALUES

(1, 'RoleID', @OldRoleID, @NewRoleID),

(1, 'Username', @OldUsername, @NewUsername);

Problem

Running the script would produce the following error:

Conversion failed when converting the varchar value 'Juan' to data type int.

But the question is why? ‘Juan’ is of type varchar(25) and it is being inserted into a varchar(max) column. Why is it being converted to data type int at all?

Investigation

Apparently, since the INSERT statement contains multiple rows, and one of the rows is inserting data type ints (@OldRoleID and @NewRoleID) into varchar columns (OldValue and NewValue respectively), SQL is now expecting all rows to have data type int in those columns.

The int gets inserted into varchar, no problem. So far, so good. But now, it’s looking for int in the other rows too! So when it sees ‘Juan’ it now attempts to convert it to int before inserting, and that’s where the error occurs!

Solution

The solution is to explicitly convert the int into varchar, since we’re inserting into a varchar column. Thus, the INSERT block now becomes:

INSERT INTO dbo.AuditTrail VALUES

(1, 'RoleID', CONVERT(varchar(1), @OldRoleID), CONVERT(varchar(1), @NewRoleID)),

(1, 'Username', @OldUsername, @NewUsername);

It should now be able to insert the two rows without a hitch. Using separate INSERT statements would also do the trick:

INSERT INTO dbo.AuditTrail VALUES

(1, 'RoleID', @OldRoleID, @NewRoleID);

INSERT INTO dbo.AuditTrail VALUES

(1, 'Username', @OldUsername, @NewUsername);

I encountered this error while making a trigger that would log changes in an audit trail. Actual script involved retrieving the values from the inserted and deleted tables of the SQL Server, but to simplify and maintain project confidentiality, the values were SET in this example.