I need to execute a SQL Server stored procedure from Java/Spring, and the SP has a try/catch block that logs errors to a table. The logging is required because the SP is also called manually or from a database task, however in my case, it creates an issue because the call from Spring is transactional (see
https://stackoverflow.com/a/15984867/302151).
Is a ROLLBACK in the catch block sufficient to address my problem, and if so, is it a good practice? I mean:
- BEGIN CATCH
-
IF XACT_STATE() != 0
-
ROLLBACK TRAN
-
INSERT INTO TBL_ERROR ...
-
END CATCH
I read the
scaler post, but I'm not sure if it's feasible to execute SQL SP from Spring without an automatically formed transaction.
What is the proposed solution if it does not work?