Transactions in Stored Procedures

[Terpsfan]

I'm getting pretty good at writing stored procedures, but I'm wondering about whether it's a good idea to have a transaction, rollback, commit in every sort of stored procedure. Or whether using transactions is something you want to use in only certain kinds of stored procedures.

 

[tlbroadbent]

1) No need for transactions in stored procedures that do not update data.

2) The default transaction mode for SQL Server is AUTOCOMMIT. "Every Transact-SQL statement is committed or rolled back when it completes." Thus a simple update SP usually doesn't require an explicit transaction because SQL Server handles.

3) For more complex SPs where you need to make sure all upudate statements work properly, it is important to use explicit transactions.

I frequently use explicit transactions when performing mass updates in batches. For example, if I need to update 1 million rows, I will perform the update in 10 batches of 100K rows. Each 100K batch will be a transaction.

Hope this brief explanation helps. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.