CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Suwaid Akbar
Suwaid Akbar

Reputation: 139

terminate the process upon raiserror sqlserver

i need to know how do i make the process stop upon encountering a raiserror command

i tried with different codes for state like 1,-1 with 16 as severity. but it does not stop the process. It displays the error and continues with the next step.

I want a way to stop the rest of the process once raiserror is encountered

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

Upvotes: 0

Views: 314

Answers (1)

Thom A
Thom A

Reputation: 96015

Use THROW and 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;

Upvotes: 1

Related Questions