Understanding Transactions - Beginning C# 5.0 Databases - page 121

create procedure sp_Trans_Test
@newpersonid nvarchar(5),
@newfirstname nvarchar(10),
@newcompanyname nvarchar(15),
@oldpersonid nvarchar(5)
as
You also declare three local variables:
declare @inserr int
declare @delerr int
declare @maxerr int
These local variables will be used with the stored procedure, so you can capture and display the
error numbers returned, if any, from the INSERT and DELETE statements.
You mark the beginning of the transaction with a BEGIN TRANSACTION statement and follow it with
the INSERT and DELETE statements that are part of the transaction. After each statement, you save the
return number for it.
begin transaction
-- Add a person
insert into person (personid, firstname, company)
values(@newpersonid, @newfirstname, @newcompanyname)
-- Save error number returned from Insert statement
set @inserr = @@error
if @inserr > @maxerr
set @maxerr = @inserr
-- Delete a person
delete from person
where personid = @oldpersonid
-- Save error number returned from Delete statement
set @delerr = @@error
if @delerr > @maxerr
set @maxerr = @delerr
Error handling is important at all times in SQL Server, and it's never more so than inside
transactional code. When you execute any T-SQL statement, there's always the possibility that it may not
succeed. The T-SQL @@ERROR function returns the error number for the last T-SQL statement executed. If
no error occurred, @@ERROR returns zero.
@@ERROR is reset after every T-SQL statement (even SET and IF ) is executed, so if you want to save an
error number for a particular statement, you must store it before the next statement executes. That's
why you declare the local variables @inserr , @delerr , and @maxerr .
If @@ERROR returns any value other than 0, an error has occurred, and you want to roll back the
transaction. You also include PRINT statements to report whether a rollback or commit has occurred.