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.
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