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:

Category(s): Database, SQL Server
Tags: , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *