Errors in converting data types in SQL commands

Errors in converting data types in SQL commandsreproduced

Problem Statement:

Perform

SELECT * from xxx_original_20031205

where msgid=62010388000012

statement, SQL Server reported an error: "An error occurred while converting data type varchar to numeric. "What does that mean?" MsgId the type of this field is: varchar (30). Environment: Database server: Microsoft SQL Server 2000 and 7.0; database server Patch: Microsoft SQL Server ServicePack1;

Cause Analysis:

Instead of suddenly being automatically converted from a number to a string by SQL Server, there is only a problem with the numeric value of the field, which is also related to the actual stored string in this field.

You see, I execute this SQL statement is no problem and can be automatically converted:

SELECT * from xxxx_original_20031205

where recordid=62010388000012

RecordID the type of this field is also: varchar (30). Why is this possible? Why? This is because the true value of the MsgId field is similar to the string "12051113280101053509", because your SQL command requires a string to match the number 62010388000012 we provide, So SQL Server by default to have so many "12051113280101053509" first converted to a number, and then to match 62010388000012. (First this involves an efficiency problem, converting so many msgid into numbers, and then matching your numbers, it will be a great waste of it) of course, this time SQL Server can not turn, because "12051113280101053509" for the number is too big, out of range , so you see SQL Server reports "error converting data type varchar to numeric", referring to the fact that the varchar (30) of the historical data "12051113280101053509" is turned into numeric, Instead of passing your SQL script The parameter 62010388000012 conversion failed. Let's look at another form of error, which is even clearer: we do

SELECT * from xxxx_original_20031205

where msgid=120

The command will get an error: the conversion of the varchar value ' 12050003010101026986 ' overflows the int column. The maximum integer value is exceeded. Does this error clearly illustrate what SQL Server does when it helps you execute SQL commands? He's trying to help you take the initiative to convert this field in the record into the type of data you indicated in the SQL command.

My advice:

Most of the time, we don't bother to see what type of a field it is, char, or tinyint, or bool, or varchar, we just write a number and let the smart SQL Server decide what to turn to.

However, first, SQL Server does not convert the numeric values in your script commands, but instead transforms the existing historical data to the type you specify, so it increases execution time, and second, easy conversion error. So, do not let SQL Server self-judgment, automatically help you convert, that will reduce the efficiency of execution, and increase the probability of error. You can explicitly tell SQL Server about your datatype, be sure to do so.

Errors in converting data types in SQL commands