A parameterised SP runs fine on my test database wth a certain set of input values. On the customers's production database with the same input values this error occurs: "Conversion failed when converting the nvarchar value 'August 2012' to data type int.'"

A parameterised SP runs fine on my test database wth a certain set of input values. On the customers's production database with the same input values this error occurs: "Conversion failed when converting the nvarchar value 'August 2012' to data type int.'"

I wonder if you would re-read my previous post? To confirm what is in it.

What is to re-read? The conversion by SQL Server was from nvarchar to int, that's why you got the error.

There is a hierarchy to the datatypes which SQL Server uses when doing implicit conversions. Unfortunately I am having problems with my google-fu in trying to find the appropriate reference in Books Online to provide that information to you.

When SQL compares column values with a variable value where the values are diferent data types.

There are 2 posibel methods:

1 Coerce each column value as it is read row by row from the table and compare to the variable2 Coerce the variable value, cahe the coerced value. Compare tow by row the column value with the cached coerced value

The most efficient method would appear to be 2 as the coercion is only done once.

ALSO

My test database has always had a column with the value 'AB' - and yet the SP has never failed before.

There is clearly something odd going on here. MY suspicion is that it to do with how SQL optimises qurey plans

Implicit conversions, which is what you have when datatypes don't match (like int and varchar) will always attempt to use the higher datatype in precedence. Look at the list and you will notice that int is much than varchar. This is one of the reasons you should not allow implicit conversions. If you want to compare to a varchar value you should make your parameter's datatype match.