Using RAISERROR not working in SQL Server 2005?

Question

I have the following code below:

BEGIN TRY BEGIN TRANSACTION -- DO SOMETHIING COMMIT TRAN END TRY BEGIN CATCH IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTION RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()) --ERROR: Incorrect syntax near 'ERROR_MESSAGE'. END CATCH 

However, the RAISERROR instruction does not work. What is wrong with the raise error statement?

Answer 1

RAISERROR follows the same rules as any other stored procedure call. The parameters passed must be a constant or a variable. You cannot pass a function directly as a parameter. See Running Stored Procedures for documentation on this.

 /* Demo Code - Functions accept functions as parameters while stored procedures do not */ create function dbo.fnDayOfWeek (@date datetime) returns int as begin declare @x int set @x = DATEPART(day,@date) return (@x) end go /* Both statements are successful */ select dbo.fnDayOfWeek('2010-08-06') go select dbo.fnDayOfWeek(GETDATE()) go drop function dbo.fnDayOfWeek go create procedure DayOfWeek @date datetime as begin select DATEPART(day,@date) end go /* First call succeeds, second fails */ exec DayOfWeek @date = '2010-08-06' go exec DayOfWeek @date = getdate() go drop procedure DayOfWeek go 

Answer 2

The error occurs because you directly use the function in RaiseError , so to avoid this, try the code below Try this for me.

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