Transactions and Stored Procedures

Because SQL code in stored procedures runs locally on the server, it is recommended that transactions be coded in stored procedures to speed transaction processing. The less network traffic going on within your transactions, the faster they can finish.

Another advantage of using stored procedures for transactions is that it helps avoid the occurrence of partial transactions—that is, transactions that are started but not fully committed. It also avoids the possibility of user interaction within a transaction. The stored procedure keeps the transaction processing completely contained because it starts the transaction, carries out the data modifications, completes the transaction, and returns the status or data ...

Get Microsoft® SQL Server™ 2000 Unleashed, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.