how to raise an error and test rollbacks

I put this at the end of a stored procedure in which I update two
tables. I am trying to raise an error so that I can test whether or not
both tables will be rolled back if the event of error.
So far, this is not working to raise and error, and the therefore the
two tables are updating. I copied this out of the SQL Server Books
online
Help? And thank you.
--SQL transact that creates the procedure,
--inserts data in one table,
--retrieves value of identity column
--and then updates a second table
--and then attempts to provoke an error
--and rollback in both tables with:
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
END
GORAISERROR is for raising the error to the calling app. At the point you are
"raising" the error, you have already determined that something is wrong in
the transaction :P This is not what you are looking for here.
In QA, if you have permissions you can right click on your proc and choose
debug to walk through it step by step. Set the values to something you know
will error and see if it goes into the rollback stage.
If you do not have permssions for debugging, just try inserting a row with
that you know will cause an error (duplicate PK for instance). In your error
handler section Print 'ROLLBACK'. Then execute the proc and check messages.
HTH,
John Scragg
"JJ_377@.hotmail.com" wrote:
> I put this at the end of a stored procedure in which I update two
> tables. I am trying to raise an error so that I can test whether or not
> both tables will be rolled back if the event of error.
> So far, this is not working to raise and error, and the therefore the
> two tables are updating. I copied this out of the SQL Server Books
> online
> Help? And thank you.
> --SQL transact that creates the procedure,
> --inserts data in one table,
> --retrieves value of identity column
> --and then updates a second table
> --and then attempts to provoke an error
> --and rollback in both tables with:
> RAISERROR ('sprStoredProcedureError', 16, 1)
> Set @.STRERROR = @.@.ERROR
> Select @.STRERROR
> IF @.STRERROR = 0
> COMMIT TRAN
> ELSE
> ROLLBACK TRAN
> END
> GO
>|||Usually, it helps to include a complete script that can be used to reproduce
the problem. Below is one that I used; it is based almost entirely on your
code snippet and works correctly. There must be another issue that is not
apparent from the information you posted.
set nocount on
go
create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
null)
go
create table aa_junk2 (junk2_id int identity, junk1_id int not null,
addl_stuff varchar(20) not null)
go
alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
go
alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
go
create procedure aa_junk as
declare @.STRERROR int
declare @.newid int
begin tran
insert aa_junk1 (more_stuff) values ('test1')
set @.newid = @.@.IDENTITY
insert aa_junk2 (junk1_id, addl_stuff)
values (@.newid, 'test1a')
select * from aa_junk1
select * from aa_junk2
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
BEGIN
PRINT 'COMMITTING'
COMMIT TRAN
END
ELSE
BEGIN
PRINT 'ROLLING BACK'
ROLLBACK TRAN
END
GO
exec aa_junk
go
select * from aa_junk1
select * from aa_junk2
go
drop table aa_junk2
drop table aa_junk1
drop procedure aa_junk
go|||Write correct code
begin tran
insert aa_junk1 (more_stuff) values ('test1')
SET @.STRERROR = @.@.ERROR -- @.@.ERROR must be saved immediately after DML
statement
IF @.STRERROR = 0
BEGIN
SET @.newid = SCOPE_IDENTITY() -- DONT USE @.@.IDENTITY !!!
insert aa_junk2 (junk1_id, addl_stuff) values (@.newid, 'test1a')
SET @.STRERROR = @.@.ERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
BEGIN
RAISERROR('AA_JUNK2 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
ELSE
BEGIN
RAISERROR(' AA_JUNK1 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
Kuido
Scott Morris wrote:
>Usually, it helps to include a complete script that can be used to reproduce
>the problem. Below is one that I used; it is based almost entirely on your
>code snippet and works correctly. There must be another issue that is not
>apparent from the information you posted.
>set nocount on
>go
>create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
>null)
>go
>create table aa_junk2 (junk2_id int identity, junk1_id int not null,
>addl_stuff varchar(20) not null)
>go
>alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
>go
>alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
>go
>create procedure aa_junk as
>declare @.STRERROR int
>declare @.newid int
>begin tran
>insert aa_junk1 (more_stuff) values ('test1')
>set @.newid = @.@.IDENTITY
>insert aa_junk2 (junk1_id, addl_stuff)
>values (@.newid, 'test1a')
>select * from aa_junk1
>select * from aa_junk2
>RAISERROR ('sprStoredProcedureError', 16, 1)
>Set @.STRERROR = @.@.ERROR
>Select @.STRERROR
>IF @.STRERROR = 0
>BEGIN
> PRINT 'COMMITTING'
> COMMIT TRAN
>END
>ELSE
>BEGIN
> PRINT 'ROLLING BACK'
> ROLLBACK TRAN
>END
>GO
>exec aa_junk
>go
>select * from aa_junk1
>select * from aa_junk2
>go
>drop table aa_junk2
>drop table aa_junk1
>drop procedure aa_junk
>go
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1