Trying to use CASE statement to associate @@ERROR code to text message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trying to use CASE statement to associate @@ERROR code to text message

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-02 : 08:00:34

Daniel writes "I'm trying to return to an ASP from a stored proc the specific error message that belongs to an error code that is non-zero subsequent to performing an insert.
I found (in The Guru's Guide to Transact-SQL manual) what I believe to be the exact text error message when an insert returns a non-zero result. Consequently, I thought of creating a case statement that will set the text corresponding to the code.
However, I can not seem to get this code to work. Here is the section of code that is not working:

IF (RTRIM(@FILENAME) <> '') BEGIN
INSERT TBL_FILE (ITEM_ID, filename, DESCRIPTION, SIZE_K, CREATED)
VALUES (@ITEM_ID, @FILENAME, @DESC, @FILESIZE, GETDATE())

set @RetVal = @@ERROR
IF (@RetVal <> 0) BEGIN
set @error_text =
CASE @RetVal
WHEN -1 THEN 'Object Missing'
WHEN -2 THEN 'Data type error occurred'
WHEN -3 THEN 'Process chosen as deadlock victim'
WHEN -4 THEN 'Permission error'
WHEN -5 THEN 'Syntax error'
WHEN -6 THEN 'Miscellaneous user error'
WHEN -7 THEN 'Resource error'
WHEN -8 THEN 'Non fatal internal error'
WHEN -9 THEN 'System limit reached'
WHEN -10 THEN 'Fatal internal inconsistency error'
WHEN -11 THEN 'Fatal internal inconsistency error'
WHEN -12 THEN 'Corrupt table or index'
WHEN -14 THEN 'Hardware error'
ELSE 'Undefined error: Could not add file to database'
ROLLBACK TRANSACTION
END
ELSE BEGIN
COMMIT TRANSACTION
SET @ERROR_TEXT = 'File Added to Database'
END
END
When I check the syntax in Query Analyzer, I get the error message:
Incorrect syntax near the keyword "ROLLBACK"
I can't figure out what's wrong with this CASE statement."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-02 : 08:02:51

Your CASE expression was not ended properly:

IF (@RetVal <> 0) BEGIN
set @error_text =
CASE @RetVal
WHEN -1 THEN 'Object Missing'
WHEN -2 THEN 'Data type error occurred'
WHEN -3 THEN 'Process chosen as deadlock victim'
WHEN -4 THEN 'Permission error'
WHEN -5 THEN 'Syntax error'
WHEN -6 THEN 'Miscellaneous user error'
WHEN -7 THEN 'Resource error'
WHEN -8 THEN 'Non fatal internal error'
WHEN -9 THEN 'System limit reached'
WHEN -10 THEN 'Fatal internal inconsistency error'
WHEN -11 THEN 'Fatal internal inconsistency error'
WHEN -12 THEN 'Corrupt table or index'
WHEN -14 THEN 'Hardware error'
ELSE 'Undefined error: Could not add file to database' END
ROLLBACK TRANSACTION
END
ELSE BEGIN
COMMIT TRANSACTION
SET @ERROR_TEXT = 'File Added to Database'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources