0

Is there any way I can check for an error in a sub-query just like we check for Null using IsNull?

For example, I need something like:

Select IsError((Select A From ABC Whete X=123), 0) ColA, ColB From MyTable

As shown, there could be a syntax error in the sub-query, a conversion error at run-time or even a missing parameter. The sub-query is injected at run-time and it's not possible to determine validity beforehand.

If the sub-query fails for any reason, I should get 0 returned in ColA.

Yes, I know that's dangerous, injecting a subquery. The sub-query is expected be correct in 99.9% of the cases. But I want to catch any error if all it happens.

navigator
  • 1,678
  • 16
  • 29
  • No, before a batch is run it is parsed; if it contains syntax errors then an error will be raised and the batch will not be run. – Thom A Apr 13 '21 at 18:22
  • 1
    As for conversion errors, look at the `TRY_CONVERT`/`TRY_CAST` functions. – Thom A Apr 13 '21 at 18:24
  • 1
    *"The sub-query is injected at run-time and it's not possible to determine validity beforehand."* This, is your real problem. It also sounds like a huge security flaw. – Thom A Apr 13 '21 at 18:24
  • 1
    As @Larnu already stated, something is wrong about the concept of the injection itself. For what it's worth: The only way to find out if an error will occur is to actually execute the query. With a little (or in some cases a lot) overhead, you could "pre-run" the subquery to check if that works before you issue the "full" query. You could optimize a little bit here by using `SELECT 1 WHERE EXISTS (SELECT ...yourstuff....))` – Kaii Apr 13 '21 at 18:40
  • Thanks @Larnu. The sub-queries are actually custom calculations that would be written by developers and they would come from another column. They will be fully safe to execute. I was just wondering if there was any way to catch a run-time error and not have the whole statement fail. – navigator Apr 13 '21 at 19:08
  • *" They will be fully safe to execute"* Then your question is moot; your code is clearly valid as it's fully safe to execute and thus you have validated it for validity and injection already. The fact that you say, however, that the query *could* be invalid means that it could *easily* be an injection issue. – Thom A Apr 13 '21 at 19:13
  • Run `SET NOEXEC ON; ...$subquery...; SET NOEXEC OFF;` and check for errors before you run the actual query https://stackoverflow.com/a/3276146/43959 – Kaii Apr 13 '21 at 19:24

0 Answers0