CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Wildfire
Wildfire

Reputation: 192

SQL Server / T-SQL : Raiserror cancels prior inserts

In a procedure, I want to make a test then Raiserror when it's actually the case. But before that, I want to log the error in a table. My code is like this 

CREATE PROCEDURE proc
    @val VARCHAR(50)
AS 
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    DECLARE @test VARCHAR(50)

    SELECT @test = test  
    FROM test_table 
    WHERE ...

    IF @test IS NULL
    BEGIN
        INSERT INTO log_table VALUES (@val);
        RAISERROR ('Invalid value : %i', 16, 1, @val);  
    END
END

The code compiles. When executed with a bad value, the error is raised, but the insert is cancelled.

I tried turning xact_abort and nocount on and off but had no luck.

I tried encapsulating the insert request in BEGIN TRANSACTION/COMMIT but still get the same result.

What I noticed, my log_table which has an auto-increment id, gets incremented even when those inserts are being cancelled.

How can I raise and error but still persist the insert request?

Thanks

Upvotes: 0

Views: 51

Answers (3)

Wildfire
Wildfire

Reputation: 192

So apparently, my procedure was working as expected in SQLServer side. The problem was that I was calling this procedure from Java/Spring native query method and had to be annotated with @Modifying and @Transactional since it's doing insertions. Thus when an exception is caught, it was automatically rolled back.

I didn't find a quick solution to bypass Spring's transaction. Now I think all I have to do is, catch the exception in App layer and log to the log_table in app layer too

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89424

In order to write to a log table you have to rollback any pending transaction. Otherwise your log table INSERT may be rolled back by the calling code, or may fail because the transaction is doomed.

So something like:

CREATE Procedure myproc 
    @val varchar(50)
as
begin
    set nocount on
    set xact_abort on

    begin transaction;
    begin try
       -- do stuff

       commit transaction;
    end try
    begin catch
      if @@trancount > 0 rollback;

      declare @error_message varchar(max) = error_message()
      INSERT INTO log_table values (@val);

      throw;
    end catch
end

Upvotes: 2

Thom A
Thom A

Reputation: 96015

Consider using THROW instead:

CREATE TABLE dbo.log_table (val varchar(50));

GO
CREATE PROCEDURE dbo.[proc] @val varchar(50)
AS
BEGIN


    SET NOCOUNT ON;
    SET XACT_ABORT OFF;


    DECLARE @test varchar(50); --As i never set this, it'll go into the IF

    IF (@test IS NULL)
    BEGIN
        INSERT INTO log_table
        VALUES (@val);
        THROW 51000, N'Invalid value.', 1;

    END;

END;
GO
EXEC dbo.[proc] @val = 'Some Value';

GO

SELECT *
FROM dbo.log_table;
GO


DROP PROC dbo.[proc];
DROP TABLE dbo.log_table;

DB<>Fiddle

Upvotes: 2

Related Questions