Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using RAISERROR isn't working in SQL Server 2005?

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 statement isn't working. What is wrong in the raise error statement?

like image 221
IsmailS Avatar asked Aug 05 '10 12:08

IsmailS


People also ask

How do you Raiserror in SQL?

RAISERROR can either reference a user-defined message stored in the sys. messages catalog view, or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY... CATCH construct.

Does Raiserror stop execution SQL?

RaisError does not end processing of a batch. All you need to do is put a Return after the RaisError and the batch will stop there. Errors with a severity of 20 or higher stop the transaction and cause an immediate disconnect.

What is Raiserror with Nowait?

RAISERROR WITH NOWAIT statement flushes all the buffered messages. Note this works fine for the first 500 RAISERROR WITH NOWAIT statement's only. After that SQL Server starts flushing 50 messages at a time.

What is the difference between Raiserror and throw?

According to the Differences Between RAISERROR and THROW in Sql Server: Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 7.0; whereas the journey of the THROW statement has just began with Sql Server 2012.


2 Answers

Error is occurring because you directly using function in Raiserror(notice single e) so to avoid this try the below code Try this is working 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;
like image 126
Pranay Rana Avatar answered Oct 14 '22 07:10

Pranay Rana


RAISERROR follows the same rules as any other stored procedure call. Parameters passed in must be a constant or a variable. You cannot pass a function directly as a parameter. See Executing 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
like image 8
Joe Stefanelli Avatar answered Oct 14 '22 07:10

Joe Stefanelli