question

I have a large stored procedure as below. The stored procedure gets XML as input and depending on the type of record (update or new), the procedure inserts record into different transaction tables by fetching data from the XML. Now creating a new record or updating the existing record requires this stored proc to read data for each table(more than 30) from the XML tags and inserting then in corresponding tables. My requirement is to break this stored procedure into smaller ones so that scalability and performance optimization can be achieved. Can the community suggest some best practices to achieve this? CREATE procedure xxx AS BEGIN Variables for each tag in XML Variable to store XML IF(some cond) Begin SQL to Detect the record type(update or new) based on a flag End IF(some cond) Begin SQL to insert record into t1. End . . . IF(some cond) Begin SQL to insert record into t30. END END Thanks.