1

I have a stored procedure like this which inserts records into few tables

CREATE PROCEDURE [dbo].[DEL_BPM_RT_DATA_BY_FILTERS] ()
AS
  BEGIN
    DECLARE @ARC_PROCESS_INSTANCE_DATA VARCHAR(MAX); 
    DECLARE @ARC_PROCESS_ACTIVITY VARCHAR(MAX); 
    DECLARE @ARC_BUSINESS_IDENTIFIER_STRING VARCHAR(MAX);

    SET @ARC_PROCESS_INSTANCE_DATA = 'INSERT INTO PROCESS_INSTANCE_DATA_ARC SELECT * FROM PROCESS_INSTANCE_DATA WHERE INSTANCE_ID IN (SELECT ID FROM #TEMP_PROCESS_INSTANCE)';

    SET @ARC_PROCESS_ACTIVITY = 'INSERT INTO PROCESS_ACTIVITY_ARC SELECT * FROM PROCESS_ACTIVITY WHERE INSTANCE_ID IN (SELECT ID FROM #TEMP_PROCESS_INSTANCE)';

    SET @ARC_BUSINESS_IDENTIFIER_STRING = 'INSERT INTO BUSINESS_IDENTIFIER_STRING_ARC SELECT *  FROM  BUSINESS_IDENTIFIER_STRING WHERE INSTANCE_ID IN (SELECT ID FROM #TEMP_PROCESS_INSTANCE)';

BEGIN
        EXEC (@ARC_PROCESS_INSTANCE_DATA);
        EXEC (@ARC_PROCESS_ACTIVITY);
        EXEC (@ARC_BUSINESS_IDENTIFIER_DATE);
END;

This stored procedure is called from JAVA like this.

try {
.
.
.
dbConnection.setAutoCommit(false);
CallableStatement stmt = dbConnection.prepareCall("{call DEL_BPM_RT_DATA_BY_FILTERS}");
stmt.execute();
dbConnection.commit();
} catch (SQLException e) {
e.printStacktrace();
}

Exception is not getting thrown when 

EXEC (@ARC_PROCESS_ACTIVITY);
EXEC (@ARC_BUSINESS_IDENTIFIER_DATE);

failed to execute(required tables not created so that they fail). However exception is thrown if the first executable statement fails i.e EXEC (@ARC_PROCESS_INSTANCE_DATA);

How to get exception thrown in all the cases whether it is first statement failure or any statement?

I don't want to put that stored procedure in transaction like BEGIN TRANSACTION. I'm using sql server 2008 R2

Thanks in advance.

Siva R
  • 427
  • 2
  • 8
  • 23
  • 1
    @Shiva R: Please do read the reply to the thread Gord Thompson cited: it definitely explains *WHY* you're experiencing the issue you're talking about ... and what you can do about it: https://stackoverflow.com/questions/42169951/h – paulsm4 Sep 16 '17 at 21:19
  • https://stackoverflow.com/questions/42169951/how-to-get-everything-back-from-a-stored-procedure-using-jdbc has all that I need. Thank you all.. – Siva R Sep 17 '17 at 12:30

0 Answers0