In SA 11.0.1, is there a way to have CONTAINS() return NULL if contains-query-string has a syntax error without (a) having to pre-validate the contains-query-string in my application, and without (b) it throwing an exception?
CONTAINS() seems to introduce a new situation. The query string is a very reasonably value to get by prompting the user. In most other cases of user-provided data, as long as you provide some minimal up-front editing, convert the data to the proper format, and avoid SQL-injection, an incorrect user-entered value would result in an expression using it to become NULL or the query returning unexpected values. The few cases that need extra attention (e,g, date/time format), are pretty easy to deal with.
In other words, bad user-entered data could produce poor results, but it's not hard to prevent user-entered data from causing the SELECT to actually fail. That's not the case for the query string. You are simply providing a string, and it's not easy for an application to pre-validate the syntax. In fact, it would be a very bad idea to build validation rules for the current SA version's query string syntax into your application.
If I wrap the CONTAINS() expression in a user-defined function, I could trap the error and return NULL, but I'd be keeping the optimizer from handling the CONTAINS() expression effectively.
I know how to handle SQL errors in my code. By that point, my options are much too limited - I'd like prevent it becoming that.
Are there any options or other magic that would let CONTAINS handle text query parser errors differently?
asked 29 Aug '10, 21:04
During development of the full-text feature we had several discussions about this particular issue: whether or not to return the empty set (if
At the end, we decided that it was best to return a syntax error, rather than an empty set or NULL, so that the user could properly distinguish between a valid search that failed to return any result, from a search that contained invalid syntax.
So to answer your specific questions: there is no way to issue a syntactically invalid
answered 30 Aug '10, 16:47