Apost or not Apost on parameter Value

Transact-SQL
1

Hi,

I have two sql server 2008 R2 on two server win 2008 R2, SP2 togheter, same database schema.

On server A the sql statement

select distinct DisplayType, SoftwareType, DisplaySerialNumber from LoggedDetails 
where OrganisationID <> 'OR03' 
and DisplaySerialNumber = 1111

fails as : The conversion of the nvarchar value '2535535453' overflowed an int column.

If I pass the DisplaySerialNumber as '1111' (apost 1111 apost) it run fine.

On server B the statement without the apost return the valid resultset.

Why ?

Thank's

2

From BOL :

When an operator combines two expressions of different data types, the
rules for data type precedence specify that the data type with the lower
precedence is converted to the data type with the higher precedence.

In this case the DisplaySerialNumber (nvarchar data type) is converted to the higher data type , INT of your expression 1111. So it is resulting in a conversion , which is failing because of that value '2535535453' (because is not in the INT range).

When you submit your query with '1111', no problem here ,the expression '1111'is varchar.

On server B, I believe you don't have that kind of value. So it is resulting in a successful conversion to INT , because you have small values that are in the range of INT data type