Few days ago we found out that passing in SQL parameters to a PreparedStatement can affect query optimization when we ran into a problem querying the database from our web app. We constantly got the following error from SQL Server: Adding a value to a ‘datetime’ column caused overflow. In the WHERE clause of the query we had something like DATEADD(month, 2, exam_date) and it went wrong, because there was an invalid exam_date entry in the database table (something like 0095-01-10).
The funny thing is that when we tried to run the very same query in Microsoft Query Analyzer it worked without an error. Then I wrote a short test that queried the database directly using PreparedStatement without Spring and iBatis to localize the problem and found out that when I passed parameters to it the error came, when I put the parameter values into the query itself it didn’t. So it seems that passing the parameters affected the order in which the conditions in the WHERE clause were evaluated: without the parameters another condition was evaluated first so that the errorneous date entry was filtered out before coming to the condition which used DATEADD.