General information
Forum:
Microsoft SQL Server
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,
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only