SQL Server stored procedure throws multiple errors

Question

I have a stored procedure where I do an INSERT and then a RAISERROR("MyException", 5, 5) in this case the insert fails.

The problem is that the result to my .NET application is

MyException: Cannot insert the value NULL...

So it returns 2 exceptions in one.

My .NET code have always just matched the entire string against "MyException" but that do not work anymore.

Is this standard? And if so, how could this have been working before? Is there any settings for this?

edit :

Im using .NET table adapter to work with the SQL database.

Version

Product : Microsoft SQL Server Enterprice(64-bit) Version : 11.0.2100.60 Cluster : False HADR : False

Microsoft Framework .NET 4.6.2

Stored Procedure

ALTER Procedure [dbo].[up_kod_text_save]
            -- Add the parameters for the stored procedure here
            @entity_id int,
    @text varchar(300),
            @kod_key int,

            @kod_id int,
    @korttext varchar(10),
    @inaktiv bit,
    @primar_extern_kod varchar(300),
    @sparad_av varchar(128),
    @kod_typ_id int,
    @kod varchar(20),
    @original_rowver rowversion,
    @associerat_varde decimal(18,5),
    @beskrivning varchar(2000),
    @viktigt_varde bit
AS

BEGIN
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;
    DECLARE @resultat table(kod_id int,  uppdat_tidpunkt datetime, rowver binary(8) );

            Insert into @resultat 
                         exec up_kod_save @kod_id,@text,@korttext,@inaktiv,@primar_extern_kod,@sparad_av,@kod_typ_id,@kod,@original_rowver, @associerat_varde, @beskrivning,@viktigt_varde

            declare @uppdat_tidpunkt datetime 
            declare @rowver rowversion

            declare @tablename varchar(30)
            declare @idname varchar(30)

            SET @rowver = (SELECT rowver FROM @resultat)
            SET @kod_id = (SELECT kod_id from @resultat)

------------------------------------------------------------------------------------
            set @uppdat_tidpunkt = getdate()

            IF(@kod_key = 2)
            BEGIN
            ELSE IF(@kod_key = 11)
            BEGIN
                         IF  EXISTS ( SELECT akut_checklista_id FROM akut_checklista WHERE akut_checklista_id = @entity_id )
                         BEGIN
                                     UPDATE akut_checklista
                                     SET [text] = @text, 
                                     [kod_id] = @kod_id
                                     WHERE akut_checklista_id = @entity_id
                         END       
                         ELSE
                         BEGIN   
                                     -- Skapa master-rad
                                     INSERT INTO [akut_checklista] ([text], [kod_id])
                                     VALUES (@text, @kod_id);
                                     set @entity_id = SCOPE_IDENTITY()
                         END
            END
            ELSE
            BEGIN
                         RAISERROR ('MyApp_EXCEPTION_UPPDATERAD_AV_ANNAN',16,1)
                         RETURN
            END


            SELECT @entity_id as entity_id, @rowver as rowver, @kod_id as kod_id, @uppdat_tidpunkt as uppdat_tidpunkt

The up_kod_save is only updating\inserting without any transaction. It might however fail if rowversion is wrong.

Answer 1

Errors with a severity of 11 or higher that occur during batch execution are returned to the application as a SqlException when the exception is raised by the SqlClient API. The individual errors are returned in the SqlException.Errors collection with the SqlException.Message containing the concatenated text of the individual errors of the collection.

Some SQL Server errors will terminate the batch so no subsequent statements in the batch are executed after the errors. In that case, only errors that happened before the batch terminating error are returned. All errors will be returned when multiple errors occur and none are batch terminating. So, depending on the specific error, the subsequent RAISERROR might not be executed.

Keep in mind that the default behavior is that SQL Server will continue to execute T-SQL statements in both your inner and outer procedures of no batch-terminating errors occur, including RAISERROR with severity 16. This could result in multiple errors returned.

Usually one doesn't want to continue batch execution after errors and raise a single error. This can be avoided by one or more of the following techniques:

• Use Structured error handling (TRY/CATCH) and use THROW instead of RAISERROR

• Check @@ERROR after each statement along with control flow statements

• Specify SET XACT_ABORT_ON so that non batch terminating errors are promoted to batch-terminating errors and transaction is rolled back

SET XACT_ABORT_ON is a best practice in stored procedures with explict transactions to ensure the transaction is rolled back in the event of a client timeout or query cancel. The batch will also be terminated unless a TRY/CATCH block is in scope.

In your case, I suggest you add a TRY/CATCH block to the outer proc. That CATCH block will be executed following an error in either the inner or outer proc. The CATCH block code can then raise the original error using THROW (SQL Server 2012 and later) or a user-defined error with RAISERROR severity 11+ to raise the exception in the client application. Below is an example.

CREATE PROCEDURE [dbo].[up_kod_text_save]
    -- parameters here
AS
SET NOCOUNT ON;
BEGIN TRY
    -- code here
END TRY
BEGIN CATCH --catch block will be entered after an error in either proc
    IF @@TRANCOUNT > 0 ROLLBACK; --needed only if BEGIN TRAN is used
    THROW; --this will raise the original error
END;
GO