Check if a certain value already exists in an SQL Server table

Asked

Viewed 769 times

2

I am trying to check whether a certain value is already inserted in my table or not. Example : Check to be determined Imei already exists in the column Deviceimei. inserir a descrição da imagem aqui

I am trying to select Count, to return 0 if Imei is not inserted in the table. Example: inserir a descrição da imagem aqui

However this is not working, it is returning me the error : The conversion of the varchar value '352093084525269' caused l'overflow of a column int.

Note: The table is called Avl_ignition and the Deviceimei field is a varchar .

I wonder if using select Count is efficient ? I would also like to know what is the cause of this error ?

sql-server
  • 4

    it seems that the error is in where, the Deviceimei field should be text, already tried where DeviceIMEI = '123'?

  • @Ricardo Pontual had already tried, but with double quotes. I just tried with single quotes and it worked. Thanks for the time.

1 answer

2


Solution:

As stated by Ricardo Pontual in the comments, it is necessary to quote 123

Explanation

When comparing two different types, an implicit conversion is made.

As said in documentation:

When an operator combines two expressions with different data types, the data type with the lowest precedence will be converted to the highest precedence data type.

According to the same documentation, the order of precedence of the varchar (352093084525269) is less than int (123), so SQL SERVER tries to convert the value of DeviceIMEI for int.

Since the int limit (2,147,483,647) is less than the Devicemei value (352,093,084,525,269), the overflow error of the int.

Put the 123 in quotes makes it read as varchar, avoiding this conversion.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.