[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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
0
tf842
Asked:
tf842
  • 3
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
rafranciscoCommented:
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now