Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: 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.


  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Your assumption/theory is correct. The batch executes till the end or the first logically reachable RETURN.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    May 2004
    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
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts