May 13, 2005 at 3:53 pm
If I run the following piece of code through QA it seems to stop before the test for @@ERROR.
BEGIN
DECLARE @errorcode int
select CAST ('Tuesday, May 10, 2005' AS smalldatetime)
SET @errorcode = @@ERROR
IF @errorcode <> 0
Print 'Oh, Dear!!!'
END
The CAST statement generates the error message
Server: Msg 295, Level 16, State 3, Line 3
Syntax error converting character string to smalldatetime data type.
However my message does not print.
Am I missing something with regard to @@ERROR and certain error levels? My understanding is that @@ERROR should be set after each TSQL statement.
Thanks in advance
Ian
May 13, 2005 at 4:30 pm
This is a very complicated subject but has been explained in full by SQL Server MVP Erland Sommarskog in two article named "Error Handling in SQL Server – a Background" and "Implementing Error Handling with Stored Procedures"
Effectively, you cannot capture all errors within a stored procedure, trigger, udf or batch.
The recommendation is to "set xact_abort on" and have all error handling performed by the client program.
SQL = Scarcely Qualifies as a Language
May 13, 2005 at 6:43 pm
Since the cast statement: CAST ('Tuesday, May 10, 2005' AS smalldatetime) has error, try this
cast('5/10/2005' as smalldatetime)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply