Database Reference
In-Depth Information
2. In Object Explorer, select the SQL2012Db database, and click the New Query
button.
3. Create a stored procedure named sp_Trans_Test using the code in Listing 8-3.
Listing 8-3. spTransTest
create procedure sp_Trans_Test
@newpersonid nvarchar(5),
@newfirstname nvarchar(10),
@newcompanyname nvarchar(15),
@oldpersonid nvarchar(5)
as
declare @inserr int
declare @delerr int
declare @maxerr int
set @maxerr = 0
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
-- If an error occurred, roll back
if @maxerr <> 0
begin
rollback
print 'Transaction rolled back'
end
else
begin
commit
print 'Transaction committed'
end
print 'INSERT error number:' + cast(@inserr as nvarchar(8))
print 'DELETE error number:' + cast(@delerr as nvarchar(8))
 
Search WWH ::




Custom Search