SQL Statment Termination using RAISERROR

Question

(SQL 2005) Is it possible for a raiserror to terminate a stored proc.

For example, in a large system we've got a value that wasn't expected being entered into a specific column. In an update trigger if you write:

if exists (select * from inserted where testcol = 7) begin raiseerror('My Custom Error', 16, 1) end

the update information is still applied. however if you run

if exists (select * from inserted where testcol = 7) begin select 1/0 end

a divide by 0 error is thrown that actually terminates the update. is there any way i can do this with a raiseerror so i can get custom error messages back?

Answer 1

You should check for valid data prior to performing the update.

IF (@testvalue = 7)
    RAISERROR("Invalid value.", 16, 1);
ELSE
    UPDATE...

Answer 2

Can you not just add a CHECK constraint to the column to prevent it from being inserted in the first place?

ALTER TABLE YourTable ADD CONSTRAINT CK_No_Nasties
    CHECK (testcol <> 7)

Alternatively you could start a transaction in your insert sproc (if you have one) and roll it back if an error occurs. This can be implemented with TRY, CATCH in SQL Server 2005 and avoids having to use a trigger.

Answer 3

In a trigger, issue a ROLLBACK, RAISERROR and then RETURN.

see Error Handling in SQL Server - Trigger Context by Erland Sommarskog