I have a stored procedure that I would like to optimize. Since we're writing to multiple tables for a single transaction under a variety of cases (some which net in insert statements, others which net in update statements), I would like to divide the labor of this stored procedure into one main stored procedure with smaller other procedures addressing specific tasks--but still retaining the integrity of the rollbacks under a saved transaction. In the Oracle world, I have the concept of packages available-- I do not see an exact equivalent in the sql server world. BTW, the current stored proc works, but is unoptimized for maintenance and performance.
Any words of advice for approaching this?