devxlogo

Make RAISERROR Your Last Command in Stored Procedures or Triggers

Make RAISERROR Your Last Command in Stored Procedures or Triggers

If you execute a SQL statement in a stored procedure after your use the RAISERROR command, you will clear @@ERROR. This is true even if you use a RETURN and return a non-zero value in the stored procedures.For example:

 DECLARE @intReturn int     , @vcErrorMessage varchar(255)     , @BeginTransCount intSELECT @BeginTransCount = @@TRANCOUNT -- line above is needed if transaction processing-- run some SQL commandIF @@error  0BEGIN     SELECT @intReturn = -1          , @vcErrorMessage = "An error occurred performing some operation."     GOTO errorEND-- more code...error:IF @intReturn IS NULLBEGIN     IF @@TRANCOUNT > @BeginTransCount COMMIT  TRANSACTIONENDELSEBEGIN     IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION     RAISERROR ( @vcErrorMessage, 16, 1)ENDGO

devx-admin

Share the Post: