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.

can you please give info about sql-server version and .net version? what about the structure os stored procedure? transaction? try/catch — MtwStark
– MtwStark, Commented Oct 9, 2017 at 10:41
My .NET code have always just matched the entire string against "MyException" but... Can you share the code that does this? — David Rushton
– David Rushton, Commented Oct 9, 2017 at 10:45
The specified severity must be 11 or greater in order for RAISERROR to throw an exception. Try RAISERROR('MyException',16,5) . Note the single quotes allow you to run with QUOTED_IDENTIFIER ON, a best practice. — Dan Guzman
– Dan Guzman, Commented Oct 9, 2017 at 10:59
I have now updated with some information. @destination-data, the .NET code is not the problem. I can see that the result from the sp is 2 error messages in one. In this case first a exception about null in akut_checklista, then the becouse of Uppdaterad_Av.... — Banshee
– Banshee, Commented Oct 12, 2017 at 12:34
have you tried to use profiler and get what your application is trying to do and put the query on SQL ? First of all I'll do this to check.. paste here your profiler insert also — Thiago Loureiro
– Thiago Loureiro, Commented Nov 2, 2017 at 10:28

Dan Guzman · Accepted Answer · 2017-11-07 12:52:08Z

10

+200

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

edited Nov 7, 2017 at 12:52

answered Nov 2, 2017 at 12:35

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SMM Over a year ago

See the Remarks section of MS Docs RAISERROR for more details. Also note from the introduction of the same link that "New applications should use THROW instead."

Banshee Over a year ago

The up_kod_save is executed and the logic in this SP will result in a RAISERROR ('MyApp_EXCEPTION_UPPDATERAD_AV_ANNAN',16,1). The strange part is that this will only throw you out of the up_kod_save and not the up_kod_text_save. The up_kod_text_save will continue to execute as if no error have been throw. Becouse the resultat is null the kod_id will also be null and that results in a new SQL exception while inserting. At this point we will be thrown out of the up_kod_text_save but with more then one exception?

Banshee Over a year ago

This have been working before(only one exception returned) so we are not sure why this behaviour have changed. Maybe some update?

Dan Guzman Over a year ago

@Banshee, I added some more information to my answer. I'm not aware of a change to SqlClient behavior. I ran some cursory tests with .NET 2.0 and later and didn't see a difference.

Collectives™ on Stack Overflow

SQL Server stored procedure throws multiple errors

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related