I've been playing around with transactions today. Not that it's the first time I've used them but I ran into some nasty nested ones. Oh, the horror of it all :(
I've been trying to change the IMPLICIT_TRANSACTIONS setting ON to prevent developers from calling stored procedures outside a transaction. The problem I'm having is that no error or warning is returned when the stored procedure gets called outside a transaction. If anyone knows a good way to solve this let me know mail me
I think I found a solution.
Checking for the number of transactions active in the stored procedure should do the trick. The transaction count is 0 if no BEGIN TRANSACTION has been executed. Adding the following code to the start of my stored procedure will probably do the trick. If anyone has a better idea or a good reason why this solution isn't a good one, please feel free to mail me
IF @@TRANCOUNT = 0