0

I'd hazard a guess that If I do it in BOTH places, there will be "limited" issues with transaction management.

I suppose a reason I can think of for having a transaction up at the application level is so that business specific logic, potentially not available in sprocs (i.e during the collation and computation of something derived from the data retrieved, or enforcment of "business rules integrity - for want of a better term) can force a rollback.

Thanks,

brumScouse
  • 3,166
  • 1
  • 24
  • 38
  • What happens if you call _two_ stored procedures and both calls should either succeed or both should fail? – Oded Dec 30 '11 at 10:52
  • There can be *big* issues with transaction management if you don't enter and exit stored procs with the same @@trancount, or if you let @@trancount go above 1 then rollback. See my answer please – gbn Dec 30 '11 at 11:09
  • thanks for the replies peeps. – brumScouse Jan 12 '12 at 12:40

2 Answers2

2

You can have transactions at both levels, if each called layer is aware of an existing transactions.

So if you expect the stored proc to be reused, you can make this aware of a transaction started from the client code, another stored proc and don't do any of begin/commit/rollback.

If it detected no transaction it can then begin/commit/rollback on it's own.

This behaviour is important because SQL Server does not really have nested or autonomous transactions: so make each stored proc safe and you don't need to think about it.

The pattern in my answer here will detect a transaction and do exactly this:
Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

It is okay to do that. If you'd use your stored procedures outside of your core business logic/services, you still might want to have them transaction safe. If your stored procedure is part of a set of operations, it then becomes a nested transaction.

Chris
  • 7,229
  • 7
  • 41
  • 57