T-SQL Incorrect syntax near 'ERROR_MESSAGE'
Date: March 2, 2012
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