How can I get a resultset and a RAISERROR result from a SQL Server Stored Procedure when reading wit-CodePudding

I have a stored procedure which retrieves some data, does some processing and may detect an error. This test procedure 'dummies' this behaviour:

create proc proc_TEST as 
SELECT name FROM sys.tables; 
RAISERROR (N'There is something wrong with this data', 16, 1);
GO

I'm using Dapper to query the data, using C# code like this:

var rows = new List<Table>();
using (var con = new SqlConnection(connectionString)) {
    try {
        var command = new CommandDefinition("proc_TEST", commandType: CommandType.StoredProcedure);
        var reader = await con.ExecuteReaderAsync(command);
        var parser = reader.GetRowParser<Table>(typeof(Table));
        while (await reader.ReadAsync()) {
            var row = parser(reader);
            rows.Add(row);
        }
        // Do something with the rows here
    } catch (SqlException se) {
        // THIS NEVER HAPPENS
        Console.WriteLine(se.Message);
        // Do something else with the rows here
    }
}

struct Table {
    public string Name { get; set; }
}

This code never sees the error - the SqlException is never raised

If I revise the stored procedure so that it doesn't return any data:

create proc proc_TEST as 
--SELECT name FROM sys.tables; 
RAISERROR (N'There is something wrong with this data', 16, 1);
GO

Then the SqlException is caught, but clearly I have nothing in 'rows' to process.

How can I get the data AND the SqlException from my stored procedure?

CodePudding user response：

The issue is that error and info messages are returned only after the preceding result set is consumed (SELECT statement results) and you navigate to the subsequent results (which include the errors) using NextResult.

Try adding NextResultAsync to raise the error and avoid undetected SqlExcpetions:

do (
    while (await reader.ReadAsync()) {
        var row = parser(reader);
        rows.Add(row);
    }
} while(await reader.NextResultAsync());