Geek asks and answers

Cannot get @@ ERROR after EXEC () with error

See the following t-sql code

   DECLARE @iError INT
   EXEC('select * from sysobj')
   SELECT @iError = @@ERROR
   PRINT 'Error = ' + CAST(@iError AS VARCHAR(10))

After starting, it returns the error message I want.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysobj'.
Error = 208

However, if I change the request

   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 error when converting var car value to int data type.

The problem is that any afer EXEC () code is not executing.

In a real stored procedure, I have code to handle errors after PRINT. And none of these codes is executed in the second case.

Can someone tell me why this is happening?

I tested it on both SQL Server 2008 and 2005.

thank

+3
3

, , ARITHIGNORE ANSI_WARNINGS.

, .

http://www.sommarskog.se/error-handling-I.html#whathappens

, , .. , , , . .

enter image description here

+2

msdn . , EXEC(). , EXEC(), temp, .

:

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'
+2

Take a look at the TRY ... CATCH implementation for error handling. Then you can put all your error handling in the CATCH block.

+1
source

More articles:


All Articles