DECLARE @cmd VARCHAR(1000)
, @ExecError INT

CREATE TABLE #ErrFile (ExecError INT)

SET @cmd = 'SELECT * FROM MyTable WHERE ...' +
'INSERT #ErrFile VALUES(@@ERROR)'

EXEC(@cmd)

SET @ExecError = (SELECT * FROM #ErrFile)


>I execute an UPDATE statement using EXEC after I build it into a string:
>@lcSQLString = 'UPDATE ' + @@mytable + 'SET x = y ...'
>EXEC(@lcSQLString)
>
>When the UPDATE fails, @@ERROR is set to 0, because it returns the status of the EXEC command (which succedded) and not the @lcSQLString (which failed). At least, that is my explanation of the problem. If I execute the statement directly by substituting the variables with their values, @@ERROR displays the correct value.
>
>How can I capture @@ERROR within EXEC? I am trying to accomplish the following:
>
>SELECT *
>FROM mytable
>WHERE ..
>
>IF @ERROR <> 0
>
>Thank you,