CODEWITHSUNDEEP
sqlsql-server
jjhorn
jjhorn

Reputation: 53

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

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?

Upvotes: 2

Views: 3030

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

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.

Upvotes: 2

user1529235
user1529235

Reputation:

run this and verify the data types;

  sp_columns Components

Looks like 'descr' is really an integer

Upvotes: 1

Related Questions