asked on

sql 2005: stored procedures, best practices

Techies--

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?

Raja Jegan R

In SQL Server, there is no concept of Nested transactions and the master transaction controls the entire thing and hence you need to create Stored procedures in such a way that it would either do the process completely or rollback based on some other parameters.

Some alternatives:
1. If it is something related to bulk data, then try using SSIS which has some intermediary commits thereby speeding up the process
2. If it is something to do record by record, then do all manipulations in a staging table and commit in the actual tables in a single shot.

ASKER CERTIFIED SOLUTION

vinodch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paula DiTallo

ASKER

Thanks so much!