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

    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.

    DMW

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    Posts
    125
    Thank you. I just wrote a script that proved that to me. If anyone else is interested....

    Code:
    /************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

Posting Permissions

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