set @error = @@error (after exec(@sql)) strange behaviour !

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  May 20, 2006 at 9:24 am

  #112406

  I have found out that the following code:

  declare @sql varchar (100)

  declare @error int

  set @sql = 'insert into a select * from b'

  exec(@sql)

  set @error = @@error

  If @error <>0 here some code to manage error.

  does not work in a server, not from the pc where I'm writing.

  In a server a procedure has a code like that, but in the case of error (for istance: Server: Msg 2627, Level 14, State 2, Line 1

  Violation of UNIQUE KEY constraint 'xxxxxx') after exec(@sql) is executed: @@error is = 0 and so @error too and the operation If @error <>0 is skipped (because @error = 0) !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!.

  Is there any options or sql instruction to retrieve the error number after EXEC (@SQL)...

  Anyone has some answer?

  Thank

  

• Mike C

  SSC-Insane

  Points: 23224

  More actions

  May 20, 2006 at 11:12 am

  #639122

  Try sp_executesql:

  DECLARE @sql NVARCHAR(100)

  DECLARE @error INT

  SELECT @sql = N'insert into a select * from b'

  EXEC @error = sp_executesql @sql

  PRINT 'Error number: ' + CAST(@error AS VARCHAR)