Suppress transaction in stored procedure
You're describing an "autonomous transaction", which is a common question from people migrating from Oracle (which supports them) to MSSQL (which doesn't). This article explains the various options, which unfortunately aren't particularly attractive:
- A loopback linked server
- A loopback connection from a CLR procedure
- A table variable that stores the data, because they are not affected by rollbacks
- A loopback connection from an extended stored procedure (but they are deprecated anyway in favour of CLR procedures)
If none of those options are practical for you, the other alternative is to shift some control into an application and out of the database, but of course that just shifts the issue to another location. Still, it may be worth considering.