Dapper return print message from stored procedure

Question

Dapper return print message from stored procedure

c# dapper sql sql-server tsql

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.

usr4896260

View more on Stack Overflow

Renato Coqueiro · Accepted Answer · 1/14/2020 5:00:17 PM

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

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator