user_odoo user_odoo - 2 months ago 7
SQL Question

Catch error message from stored procedure

how catch error message etc "Error converting data type varchar to datetime"

and this error put in Select output

BEGIN
SET NOCOUNT ON;

begin try
UPDATE projects
SET
projectUser = @projectUser,
projectStartDate = @projectStartDate
where projectId = @projectId
SELECT 'OK'
end try

begin catch
SELECT 'ERROR - msg error'
end catch

COMMIT

END


I don't need ==> https://postimg.org/image/u8mohtl9b/

Any solution?

Answer

The issue you are having is that the error is not occuring in the procedure, it is occuring when calling the procedure. In a simple example, create the following procedure:

IF OBJECT_ID(N'dbo.ErrorCatchTest', 'P') IS NOT NULL
    DROP PROCEDURE dbo.ErrorCatchTest;
GO
CREATE PROCEDURE dbo.ErrorCatchTest @int INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DECLARE @T TABLE (I INT);
        INSERT @T (I) VALUES (5.0 / @int);

        SELECT 'OK';
    END TRY
    BEGIN CATCH
        SELECT CONCAT('Error_Message: ', ERROR_MESSAGE());
    END CATCH
END;

If I pass a valid INT to the procedure:

EXECUTE dbo.ErrorCatchTest @int = 1;

The I get OK as required.

enter image description here

If I a pass 0 to force an error:

EXECUTE dbo.ErrorCatchTest @int = 0;

enter image description here

You get the error message as required, but if I try an pass an invalid integer:

EXECUTE dbo.ErrorCatchTest @int = 'Not a number';

enter image description here

Then I get the error message, because the error is not within the proceudure, but while doing an implicit convert on the parameters.

The way around this is to call the procedure within a try/catch block:

BEGIN TRY
    EXECUTE dbo.ErrorCatchTest @int = 'Not a number';
END TRY
BEGIN CATCH
    SELECT CONCAT('Error_Message: ', ERROR_MESSAGE()); 
END CATCH

enter image description here