Raiserror in SQL Server stored procedure

Question

I already created the error in sys.message. The problem is when I add it to my stored procedure, it doesn't pass me the message back. The stored procedure checks to see if an id exists in a certain areacode if the id does not exists the raiserror should be fired.

AS
BEGIN 
    DECLARE @Result as int 

    IF EXISTS(SELECT ID, areacode
              FROM Table1 
              WHERE ID = @ID
                AND areacode = @areacode)
        RAISERROR (50030, 1, 1)

BEGIN  
   INSERT INTO Table2 ( //columns go here )
   VALUES ( //values for columns )
END

It's not clear if you except the error to be raised when the id is not found as you wrote in your description, or if it is found as written in the stored procedure. — Zohar Peled, Apr 13 '15 at 07:46

Jarle Bjørnbeth · Answer 1 · 2015-04-13T08:00:57.043

2

Your severity is to low, try to set it a bit higher: raiserror (50030,16,1)

Btw. why do you try to raise the error right before your insert?

https://msdn.microsoft.com/en-us/library/ms178592.aspx

edited Apr 13 '15 at 08:00

answered Apr 13 '15 at 07:50

Jarle Bjørnbeth

429
3
9

1

First he needs to raise the error under the right conditions. Second, read this: http://stackoverflow.com/questions/1122925/what-do-the-different-raiserror-severity-levels-mean – Zohar Peled Apr 13 '15 at 07:54
Thanks i changed it to 16.sorry i couldnt mark two answers,but very helpfull – doe Apr 13 '15 at 08:53

score 2 · Accepted Answer · answered Apr 13 '15 at 07:51

DECLARE @Result as int 

IF EXISTS(SELECT 1 
      FROM Table1 
      WHERE ID=@ID
      AND areacode=@areacode
      )
BEGIN  
    INSERT INTO Table2 (/* columns go here */)
    VALUES (/* values for columns */)
END ELSE BEGIN
    RAISERROR (50030,1,1)
END

Raiserror in SQL Server stored procedure

2 Answers2