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:

Tags: , , , ,

Friday, March 2nd, 2012 Database, SQL Server

Leave a Reply

 

Comments are moderated due to spammers. Your comments will not appear until I review and approve them. If you left a question I will answer as best I can so be sure to check back.