pub.db:call : how to have an error ?

,
1

Hello,
I have the following test SQL Code :

ALTER PROC [dbo].[sp_Test]
	@isOpen BIT 
AS BEGIN

BEGIN TRY
   DECLARE @Num1 AS INT = 1
   DECLARE @Num2 AS INT = 0
   IF @isOpen = 0 BEGIN
		BEGIN TRANSACTION
   END
   INSERT INTO TblTest (TestField) VALUES ('Test')

   SET @Num1 = @Num1/@Num2

   IF @isOpen = 0 BEGIN
		COMMIT
   END
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

	IF @isOpen = 0 BEGIN
		ROLLBACK
	END
    SELECT   
        @ErrorMessage = ERROR_MESSAGE(),  
        @ErrorSeverity = ERROR_SEVERITY(),  
        @ErrorState = ERROR_STATE();  

    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );  



END CATCH

END

I am sorry, it’s SQL (from TSQL Code). It’s not the subject. But I think u can understand that when you execute it, that generate an error.

This code :

exec dbo.sp_Test 1

generate an error and it’s work by using an Customer SQL Adapter.

But I want try a transaction.
So I have the following flowService code :

- pub.db:connect
- pub.db:startTransaction 
- SEQUENCE : Try/Catch (EXIT ON SUCCESS)
- SEQUENCE : Try (EXIT ON FAILURE)
-- pub.db:call ($dbProc = sp_Test, $dbProcSig[1].name = @isOpen, type= BIT, direction = in, $data.isOpen = 1)
-- pub.db:commit
- SEQUENCE : Catch (EXIT ON DONE)
-- pub.db:rollback

So After this flow, I notice (in debug), that after calling the pub.db:call step, the next step go to the commit statement (and not in the CATCH Sequence). I don’t know why.

Thanks for the explanation and the way to fix it.

2

Debugging a service with start/commit/rollback will not work.