April 17, 2008 at 1:41 pm
For the following SP, is table aaa doesn't exist, error is not caught. Any help is appreciated. Thanks.
I would still expect ''Unexpected error occurred!' if table 'aa' doesn't exist.
DECLARE @intErrorCode INT
BEGIN TRAN
UPDATE aa
SET DRE_EMAIL_ADDR = 'ss'
WHERE DRE_EMAIL_ADDR = 'mm'
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
April 17, 2008 at 1:45 pm
Have you tried BEGIN TRY....
Oops - just realized this is a 2000 forum - nevermind
April 17, 2008 at 1:48 pm
Yes I tried it, with TRY/CATCH It's still not get caught.
It doesn't display "Should be here ...." but just :
Msg 208, Level 16, State 1, Line 3
Invalid object name 'aa'.
And I am using 2005.
BEGIN TRY
BEGIN Transaction
UPDATE aa SET DRE_EMAIL_ADDR = 'ss' WHERE DRE_EMAIL_ADDR = 'mm'
COMMIT Transaction
END TRY
BEGIN CATCH
DECLARE @err int
SELECT @err = @@error
PRINT 'Should be here : @@error: ' + ltrim(str(@err))
ROLLBACK
END CATCH
April 17, 2008 at 3:23 pm
Found it ...
TAC block will not catch the compile errors , if it is not called in the from of dynamic query or in some SP . This still can be achieved by dynamic query.
--Use Dynamic Query to catch compile time error messages
DECLARE @error_message varchar(400)
BEGIN TRY
-- This PRINT statement will run since the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
DECLARE @sql NVARCHAR(2000)
SET @sql = 'SELECT * FROM NonExistentTable;'
-- This SELECT statement will generate an object name
-- resolution error since the table does not exist.
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
SET @error_message = 'ERROR - In the catch block - Stored Procedure Failed.';
RAISERROR(@error_message,16,1) WITH NOWAIT
END CATCH;
GO
April 17, 2008 at 4:57 pm
Going back to your original example - if you wanted an error if a table doesn't exists I would do somethin like this:
DECLARE @intErrorCode INT
IF OBJECT_ID('AdventureWorks.Person.AddressX') IS NULL
BEGIN
PRINT 'Table does not exist'
RETURN
END
BEGIN TRAN
UPDATE AdventureWorks.Person.AddressX
SET DRE_EMAIL_ADDR = 'ss'
WHERE DRE_EMAIL_ADDR = 'mm'
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply