capturing object not found in SP

**ck9663** · Apr 17 '08, 08:45 PM

Originally posted by iitt2007

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.
I am using 2005.

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

Also same results 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

I think the reason is because TAC (Try and Catch) does not handle syntax or recompilation errors that prevents a batch from executing. If you use a dynamic query, it will catch it. Or put it in some other SP. Get more details here

-- CK

capturing object not found in SP

capturing object not found in SP

Comment