I'm dealing with this custom database where an attribute for an authorization can store many things (amount approved, flags, yes/no, etc.)
For the attribute that store the amount approved, I have a job that starts off periodically to put a TermDate on it based on some business rules. I had an expression like this in the where clause: CONVERT(MONEY, @value, 1) > 10000 simply because @value comes in as a VARCHAR field.
This fails, which I can understand. it's doing a table scan and failed when converting a "YES" into MONEY.
I then built a subquery to filter out the data first and convert @value into MONEY first. The subquery works fine and brings back data the way I want. But when I substitute this subquery in place of the original table I'm using and try to compare the "converted" value, it still didn't work.
The workaround I have right now is to put the subquery result into a temp table and select out of the temp table to do my compare. This part is not that intuitive to me and I'll find out why and post a reply here.
Feel free to post your 2 cents.
Notice that helpful hint from Chris Alcock below that I forgot to mention that I tried.
[Update 2009-11-04 by Chris Alcock]
you may want to try the having your where clause handle the non-numeric cases for you, something like:
where case when isnumeric(@value) = 1 then convert(MONEY, @value, 1) else 0 end > 10000
this will cause non-numeric values to be converted to 0, but you could equally use null or some other 'magic number'
Hope that helps,
Chris