tf842
asked on
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
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
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.
ASKER
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
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
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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