By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

capturing object not found in SP

P: 13
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
Apr 17 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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
Apr 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.