Just as a point of clarity on that, SQL Server only allows one encapsulating transaction per session. You can have begins and so on within a stored procedure, b

Just as a point of clarity on that, SQL Server only allows one encapsulating transaction per session. You can have begins and so on within a stored procedure, but really that is little more than a "just in case" defense (e.g. just in case the caller didn't already do this), as you can only commit or rollback the outer transaction. If you want to have atomic transactions within a transaction you have to do hacks like make a distributed call back to yourself, in that new session creating another transaction. It's an element of SQL Server that confuses people to this day.