在raiserror sqlserver上终止进程

Question

我需要知道如何让进程在遇到raiserror命令时停止

我尝试了不同的状态代码，比如1，-1，严重程度为16。但它不会停止这一过程。它将显示错误并继续执行下一步。

我想要一种方法来停止进程的其余部分，一旦遇到raiserror

create procedure p_testing
as
begin
RAISERROR('Error is raised',16,1);
insert into blah(si,name) values(1,'teasting_raiserror');
END

Exec p_testing

Answer 1

使用THROW和XACT_ABORT

CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1), SomeString varchar(10));
GO

CREATE PROC dbo.SomeProc @SomeString varchar(10) AS
BEGIN
    
    SET XACT_ABORT ON;
    
    THROW 59000, N'A pointless error has been raised!', 16;

    INSERT INTO dbo.SomeTable (SomeString)
    VALUES (@SomeString);
END;
GO

EXEC dbo.SomeProc 'MyString';

--This statement won't be run
SELECT *
FROM dbo.SomeTable;
GO
--This will be, as in a separate batch
SELECT *, NULL AS AdditionalColumn
FROM dbo.SomeTable;
GO
--Clean up

DROP PROC dbo.SomeProc;
DROP TABLE dbo.SomeTable;