Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Stored Procedure Failed, but kept running

    HI,

    i have a sql 2008 stored procedure and the first step is to drop an index, then it does an insert into the table, then recreates the index.

    However, yesterday the procedure failed when it tried to drop the index becasue it didn't exist, which makes sense. But it kept running and still did the insert and created the index.

    Is this how a stored proc usually functions when it hits an errror? Is there a way to make it stop running the rest of the stored procedure?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In Microsoft SQL some errors are considered fatal, others are considered informational. Trying to drop an index that doesn't exist is an informational error, not a fatal error.

    You can test the result of the @@error variable, and RETURN from the procedure if you find the error offensive. For example:
    Code:
    IF 0 != @@error THEN RETURN
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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