Dapper return print message from stored procedure

Question

I have a stored procedure which I can't modify and I'm trying to get the PRINT message that get's returned if an error occurs. The code in my stored procedure is.

IF((ISNULL(@ID,''))='')
BEGIN
    PRINT N'Error: This ID is invalid.'; 
    RETURN;
END

In my dapper call, I have the following:

var storedProcResult = connection.Query("AdventureWorks.dbo.myStoredProcedure", new
{
    @ID = null
},
 commandType: CommandType.StoredProcedure
);

The value of storedProcResult is 0 but I'm interested getting the PRINT message. Please advise.

I don't think Dapper has anything specific for this. You can hook up a handler to `SqlConnection.InfoMessage` manually -- however, for error code specifically, what you really want is `THROW` / `RAISERROR` so you get an actual `SqlException`. `PRINT` messages are very easy to ignore by clients, and in fact the vast majority of them *do* ignore them. Did whoever wrote this procedure live in a land where only Management Studio was used for all work, and nobody ever ignored the "Messages" tab? Seems like something that's not viable in the long run. — Jeroen Mostert, Jan 14 '20 at 16:24
I'm about 99% (or so) sure that you are out of luck. Your sproc returns nothing. The PRINT statement simply writes to the console — Flydog57, Jan 14 '20 at 16:24
If I'm able to modify the stored procedure, what is the best approach to get error messages. Using error codes? — usr4896260, Jan 14 '20 at 16:32
@Flydog57 there is no console; it writes to the TDS stream; you can receive that via `InfoMessage` (SSMS etc just do the exact same thing internally, and present it as though there *is* a console) — Marc Gravell, Jan 14 '20 at 16:36
to confirm: @JeroenMostert is correct - Dapper makes absolutely no provision for this currently, for multiple reasons, but not least: the API for this (`InfoMessage`) is at the *connection* level, not the *command*, and Dapper is mostly interested in commands as the unit of work. Also: it isn't defined on `DbConnection`, but is instead `SqlConnection` specific (Dapper tries to be connection-agnostic as far as possible). But: just subscribe to `InfoMessage` in your code, and you should be set! — Marc Gravell, Jan 14 '20 at 16:39

score 2 · Answer 1 · answered Jun 22 '21 at 07:10

Based on the comments of Jeroen and Marc I have written a very basic code to get the messages of a single command:

var infoMessageBuilder = new StringBuilder();

void OnInfoMessageEvent(object _, SqlInfoMessageEventArgs args)
{
    infoMessageBuilder.AppendLine(args.Message);
}

// Register message event listener
cn.InfoMessage += OnInfoMessageEvent;
await cn.ExecuteAsync("AdventureWorks.dbo.myStoredProcedure", new {@ID = null}, commandType: CommandType.StoredProcedure);

// Unregister listener to stop fetching events from the connection
cn.InfoMessage -= OnInfoMessageEvent;

// Get messages as string
var message = infoMessageBuilder.ToString();

I registered an event handler right before command execution and will write all output of the InfoMessage event to a StringBuilder. The handler will be removed after execution. This works perfectly fine for me. Hope it helps someone else who will get here.

score 0 · Answer 2 · answered Jan 14 '20 at 17:00

You can use the structure bellow and just replace the value of @ErrorMessage variable.

CREATE OR ALTER PROCEDURE DBO.SPR_PROCEDURE_NAME()AS
BEGIN

    DECLARE 
     @ErrorMessage NVARCHAR(4000)
    ,@ErrorSeverity INT
    ,@ErrorState INT

    BEGIN TRY



        --YOUR CODE HERE



    END TRY

    BEGIN CATCH     
        IF (XACT_STATE()) = -1
            ROLLBACK TRAN

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),  
               @ErrorState = ERROR_STATE();  
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH

END

Dapper return print message from stored procedure

2 Answers2