Stored procedure dies before error handling when executed from Query Analyzer

Posted on 2005-04-27
Last Modified: 2010-03-19
sql server 2k

I am trying to capture a failed update, however when I run the following query in Query Analyzer, the query dies before running the error handling code. My intent is to do something similar in production to capture errors due to bad data from other systems.
exec sp_error_test

This is the stored procedure:
CREATE PROCEDURE [dbo].[sp_Error_Test]


DECLARE @Error int
DECLARE @ErrorDesc varchar(512)
DECLARE @Process varchar(256)

BEGIN Tran t1

update mytable
set fld1 = 'a'  --/////////////// fld1 is integer. I want it to fail

SELECT @Error=@@error

IF (@Error = 0)
  commit tran t1
  rollback tran t1
  SET @ErrorDesc = 'My Error Description'
  SET @Process = 'The process in the stored procedure'
    VALUES (GetDate(), @Error, @ErrorDesc,
            @Process, OBJECT_NAME(@@procid))


Thank you,
Question by:tf842
    LVL 28

    Expert Comment

    Your code looks fine.  The only possible where your error can be is in your INSERT Error_Log.  Make sure that the columns in your VALUES match the columns in your Error_Log table.  Better yet, specify the columns in your INSERT.

    Author Comment

    I should have put the exact error into the original question.

    Here it is
    Server: Msg 245, Level 16, State 1, Procedure sp_Error_Test, Line 11
    Syntax error converting the varchar value 'a' to a column of data type int.

    I usually do include the fields in the Insert statement. I added them to the code but get the same error.

    I also put a print statement directly after the
    update mytable
    set fld1 = 'a'  --/////////////// fld1 is integer. I want it to fail
    print 'after update'
    but it does not print. The code seems to die immediately upon trying to update fld1 with a non-integer value.

    LVL 28

    Expert Comment

    I believe the reason why your stored procedure stops because the error is a syntax error.  Try creating a table with unique constraint and inserting a record to the table that will violate the constraint and it should work.

    Author Comment


    The error code processes with your suggested modification.

    However, that still doesn't solve the problem, unless there is no way to catch a syntax error with @@error.

    Is it possible to capture a syntax error in a stored procedure?
    LVL 28

    Accepted Solution

    I don't think it's possible to capture syntax errors in stored procedures.  The stored procedure stops immediately when a syntax error is encountered.

    Author Comment

    I think you are correct although the terminology could be clarified.

    It appears errors created by mismatched datatypes, when incompatible, cannot be captured in a stored procedure.

    I am willing to close the question and move on. I rarely if ever mismatch data types in an update or insert unless first converting.

    Thank you for your help.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now