Errors and Stored Procs

Ok, I've read somewhere(which I'm looking for again :\ ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.

I do know I can use the @.@.error and will start using it but I need more proof to agree or not agree with my theory.

Thanks ahead of time for any information you can give me either way.

DMWYour assumption/theory is correct. The batch executes till the end or the first logically reachable RETURN.|||Thank you. I just wrote a script that proved that to me. If anyone else is interested...

/************Execute to create the tables****************/
--create test table
CREATE TABLE mytesttable
(
theid int UNIQUE NONCLUSTERED,
thedate datetime
)

--Insert values into the table
insert into mytesttable
(theid, thedate) values
(1, '1/1/2004')
insert into mytesttable
(theid, thedate) values
(2, '2/1/2004')
insert into mytesttable
(theid, thedate) values
(3, '3/1/2004')

/************************************************** */

/************Execute to create the stored proc**************/
create procedure spMytest

as
declare @.theid int
declare @.err1 int
declare @.err2 int

begin transaction thetest
--Force the unique constrant to happen
select @.theid = max(theid) from mytesttable

insert into mytesttable
(theid, thedate) values
(@.theid, '4/1/2004')
set @.err1 = @.@.error
commit transaction thetest

update mytesttable set thedate = getdate() where theid = @.theid
select @.err1

/************************************************** */

/*****************Run the next lines to watch the fun********/

--Run the stored Proc
exec spMytest
--look at the results
select * from mytesttable
/************************************************** */



Hope this helps someone else

DMW