I'm pretty frustrated! I am working with the data back end in an existing program that stores student marks (A, B, I, P, F, or numbers and some other values). I cannot control what the users put in the Marks column which is VARCHAR(10). I need to produce a query that compares numeric marks against certain values and ignores everything else - for instance, MARK > 100. It should be a single SELECT query, because the program's custom report generator simple executes that and returns the columns. However, I may be able to execute a stored procedure that returns a table with the report generator if necessary.
My problem is this: T-SQL ISNUMERIC function considers certain things numeric that are not. For instance, a hyphen. However, it will subsequently fail to convert or cast properly to a numeric data type. Most error trapping methods, like @@ERROR or TRY...CATCH, will stop the processing, in which case the custom report gives the user nothing (except the message "No tables in dataset"). I can't use TRY...CATCH within a user-defined function either. I don't want to just send the user an error message.
How can I construct a query that will simply ignore those records that fail to convert and produce a result? Also, how do I pinpoint the records that are causing the problem and perhaps report those to the user? I'm going to hate myself if I am missing something simple here....