Can not get @@ERROR after EXEC() with Error
Solution 1
Different errors will have different levels of abort and some of these can be controlled through the use of settings, like ARITHIGNORE and ANSI_WARNINGS.
I suggest reading the following article that explains all of this in great detail.
http://www.sommarskog.se/error-handling-I.html#whathappens
Statement-termination and Batch-abortion
These two groups comprise regular run-time errors, such as duplicates in unique indexes, running out of disk space etc. As I have already have discussed, which error that causes which action is not always easy to predict beforehand. This table lists some common errors, and whether they abort the current statement or the entire batch.
Solution 2
Look into implementing TRY...CATCH for your error handling. You should be able to put all of your error handling in the CATCH block then.
Solution 3
There's an article on this on msdn here. It has to do with the context in which the EXEC()
statement is run. If you include the error trap in the parameter passed to EXEC()
, you can stuff it into a temp table and then look at that.
Here's their example:
DECLARE @cmd VARCHAR(1000), @ExecError INT
CREATE TABLE #ErrFile (ExecError INT)
SET @cmd = 'EXEC GetTableCount ' +
'''pubs.dbo.authors''' +
'INSERT #ErrFile VALUES(@@ERROR)'
EXEC(@cmd)
SET @ExecError = (SELECT * FROM #ErrFile)
SELECT @ExecError AS '@@ERROR'
![EricZ](https://i.stack.imgur.com/BaIMU.png?s=256&g=1)
EricZ
Updated on June 26, 2022Comments
-
EricZ about 2 years
Please see the following t-sql code
DECLARE @iError INT EXEC('select * from sysobj') SELECT @iError = @@ERROR PRINT 'Error = ' + CAST(@iError AS VARCHAR(10))
After I run it, it returns error message, which is what I want.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysobj'.
Error = 208However, if I change the query
DECLARE @iError INT EXEC('select * from sysobjects where ''c'' = 1') SELECT @iError = @@ERROR PRINT 'Error = ' + CAST(@iError AS VARCHAR(10))
The output will be
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'c' to data type int.The problem is any code afer EXEC() is not executed.
In the real stored procedure, I have some code to handle the errors after PRINT. And none of those code executed in second case.
Could someone let me know why this happens?
I tested it on both SQL Server 2008 and 2005.
Thanks
-
EricZ about 12 yearsThanks Joe. Yes, I changed the code with TRY/CATCH, and it works. Just want to know why this happens.