Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Handling Transactions in Stored Procedures

The way that a transaction is processed in a stored procedure forces you to handle transaction processing differently within the stored procedure. Using a transaction process within a stored procedure is tricky because you will generally be using transactions in the batch that calls the stored procedure. To make sense of this, we will first create a stored procedure and see how the problems could occur. Then, I will make changes to the procedure and look at how this solves the transactional problems. For this section, we will be using the Northwind database tables. However, to save the original data, I will create two new tables using the data from the Products table.

 Create Table Demo_OrderDetails ( ...