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
RETURN (@ERROR)
ELSE
-- continue with other statements

Thank you,