Stored procedure dies before error handling when executed from Query Analyzer

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]

AS

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)
begin
  commit tran t1
end
else
BEGIN
  rollback tran t1
  SET @ErrorDesc = 'My Error Description'
  SET @Process = 'The process in the stored procedure'
  INSERT INTO Error_Log
    VALUES (GetDate(), @Error, @ErrorDesc,
            @Process, OBJECT_NAME(@@procid))

END
GO

Thank you,
Sami
tf842Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
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.
0
 
rafranciscoCommented:
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.
0
 
tf842Author Commented:
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.

Sami
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rafranciscoCommented:
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.
0
 
tf842Author Commented:
ra,

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?
Sami
0
 
tf842Author Commented:
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.
Sami
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.