[Feature] Try-catch syntax for macros

### Is this your first time submitting a feature request?

- [x] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [x] I have searched the existing issues, and I could not find an existing issue for this feature
- [x] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

I was not allowed to reopen issue [#8633](https://github.com/dbt-labs/dbt-core/issues/8633), so instead making a duplicate one, close merge as required

The original feature request was for a type of `try`/`catch` type of logic approach in jinja, and the use-case provided by original author was not prioritize, and eventually got stale and closed.

Please re-asses with below input as use-case:

when doing a `run_query()` call , or the sibling `call statement()` with a query that is not valid an exception is raised.
If many such calls are made - it may be difficult to asses from the log what was wrong and which part of the code caused the issue.

You can see the failed code from the debug-log since queries are pasted there by `run_query()` before being sent to the `adapter`, or from the database engines log which will also have a record of the failed query. Neither of these contain the context of why, from where or how it was executed.

My main aim is to have a context provided in the error message that comes out. I can see two options to get this out, I'm open for other alternatives

- with a try statement around it, that additional context would be visible if programmed to
- with an optional string parameter to the run_query(), the user can pass in a context which will be added to the exception if raised, possibly it may also be added to the query header, but that may cause other issues in case it contain characters which is not suitable for a header

### Describe alternatives you've considered

currently I'm adding an additional context log statement before each run_execute(), although this works, it is not as intuitive as the approaches suggested above.

Many of the (sql associated) issues can be captured, i.e. instead of running "" , you can

```sql
BEGIN

exceptions when others
.......
END:
```

The approach here is to capture the cases where the does not even parse.

### Who will this benefit?

users using run_query would have better tracking of what went wrong in case of a crash

### Are you interested in contributing this feature?

_No response_

### Anything else?

_No response_