Conversion failed when converting the varchar value '0%' to data type int

Question

I'm facing a problem with SQL Server.

I've created a table like this:

create table Components 
(
    pk BIGINT NOT NULL PRIMARY KEY IDENTITY,
    id VARCHAR(50),
    descr VARCHAR(50),
    in_m INT,
    in_iy INT,
    p_fw VARCHAR(5000)
);

and I'm trying to insert a couple of values:

insert into Components (id, descr, in_m, in_iy, p_fw) 
values ('FW000_A', '0%', 0, 0, '[0.0,0.0,0.0]'),
       ('FW000_B', '1%', 1, 1, '[1.0,1.0,1.0]');

I get the following error:

Msg 245, Level 16, State 1, Line 111
Conversion failed when converting the varchar value '0%' to data type int.

even though the column descr is correctly defined as varchar(50).

Can anybody help me please? Why is SQL Server trying to convert my strings to int values?

Your code works for me in SQL Server 2014. rextester.com/UOOT87695 Are you sure it's the same table you just created? Same schema, etc.? — Jacob H
I'm using SQL Server Express 2012 but yes, I have the "use mydb;" statement at the beginning of the file. EDIT: I tried to add the notation [schema].[table] for both the create and insert queries, but I'm obtaining the same result as before... — jjhorn
If you run a SQL Trace (or use hibernatingrhinos.com/products/nhprof) what is the actual SQL being passed across the wire? — mjwills
A database is not the same as a schema. Check again? Like mydb.dbo.components vs mydb.AnotherSchema.components — Jacob H
The code works fine here as well: rextester.com/live/EAL20795 — TheEsnSiavashi

Damien_The_Unbeliever Damien_The_Unbeliever · Accepted Answer · 2017-07-07T06:08:28

What's missing from your question is that you have more than just the two values lines you've shown, and one of the other ones has an integer literal for the descr column, rather than a string.

This example produces the same error:

declare @t table (Descr varchar(50) not null)
insert into @t(Descr) values
('0%'),
(12)

What I believe happens is that SQL Server first tries to determine the data types for all columns in the values clause. Using data type precedence rules, it observes a varchar literal in one row and an int literal in the other, and so determines that the overall type for this column is int and attempts to perform the conversion that leads to the error.

During this process, it does not use any information about the target table into which the values are going to be placed, including the data types of the columns there.

Conversion failed when converting the varchar value '0%' to data type int

2 Answers