stored procedure question 1

[lfc77]

If I have multiple table updates in a stored procedure and one fails, does this mean that the earlier updates are automatically rolled back, since the stored procedure is a block of code that is run as a whole?


Cheers,

lfc77

 

[NoCoolHandle]

No,that will depend on how your stored proc is written..

usually (unless you have declared a tran and called an explicit roll back) every update before will commit as well as after..

What you need to do (in the proc) is test for @@error not being 0 after every statement that could cause an error. Then explicitly roll back or ignore the error..

for instance

use crap
go
drop table x
go
create table x(c1 int primary key, c2 int not null)
go
create proc updates
as
insert into x values (1,1)
insert into x values  (1,2)
insert into x  values (2,1)
insert into x values  (3,1)
update x set c1 = 2 where c1 = 3
insert into x  values (4,1)
go
updates
go
select * from x

returns
[blue]
1 1
2 1
3 1
4 1

+ a couple of primary key errors
[/blue]

to fail on an error and have everytihing rollback

Use crap
go
drop table x
go
drop proc updates
go
create table x(c1 int primary key, c2 int not null)
go
create proc updates
as
declare @e int
begin tran 
insert into x values (1,1)
set @e = @@error
	if @e <> 0 
		begin
			if @e = 1243
				begin
--					 do sometning non standard
					select * from sysobjects
				end
			else
				begin
					rollback
					return
				end
		end
insert into x values  (1,2)
set @e = @@error
	if @e <> 0 
		begin
			if @e = 1243
				begin
--					- do sometning non standare
					select * from sysobjects
				end
			else
				begin
					rollback
					return
				end
		end
insert into x  values (2,1)
set @e = @@error
	if @e <> 0 
		begin
			if @e = 1243
				begin
--					- do sometning non standare
					select * from sysobjects
				end
			else
				begin
					rollback
					return
				end
		end
insert into x values  (3,1)
set @e = @@error
	if @e <> 0 
		begin
			if @e = 1243
				begin
--					- do sometning non standare
					select * from sysobjects
				end
			else
				begin
					rollback
					return
				end
		end
update x set c1 = 2 where c1 = 3
set @e = @@error
	if @e <> 0 
		begin
			if @e = 1243
				begin
--					- do sometning non standare
					select * from sysobjects
				end
			else
				begin
					rollback
					return
				end
		end
insert into x  values (4,1)
set @e = @@error
	if @e <> 0 
		begin
			if @e = 1243
				begin
--					- do sometning non standare
					select * from sysobjects
				end
			else
				begin
					rollback
					return
				end
		end
commit
go
updates
go
select * from x

does nothing


HTH


Rob