CODEWITHSUNDEEP
sql-serverstored-procedures
doe
doe

Reputation: 148

Raiserror in SQL Server stored procedure

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

Upvotes: 1

Views: 2643

Answers (2)

Jarle Bjørnbeth
Jarle Bjørnbeth

Reputation: 429

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

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82534

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

Upvotes: 2

Related Questions