T-SQL Incorrect syntax near ‘ERROR_MESSAGE’

Working on Microsoft SQL Server 2005 I was trying to raise an error doing the following:

BEGIN CATCH
  ...
  RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE());
END CATCH

Looks pretty straight forward to me. So why the Incorrect syntax near 'ERROR_MESSAGE' error? Poking around the net it seems parameters passed to RAISERROR must be a constant or a variable. You cannot pass a function return value directly as a parameter. Even though I’ve seen examples like this one that use the above syntax, they don’t actually work. The correct way to write the above is like this:

BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  ...
  SELECT
    @ErrorMessage=ERROR_MESSAGE(),
    @ErrorSeverity=ERROR_SEVERITY(),
    @ErrorState=ERROR_STATE();
  RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Further reading:

Related Posts

Leave a Reply