question

RaghveerArora avatar image
RaghveerArora asked

Splitting a large SQL Server stored procedure into small procedures

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.
sql-server-2012stored-proceduresoptimization
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.