Store procedure sbo_sp_transactionnotification must be Return

[Former Member]

--DROP PROCEDURE "VISIONTEST".SBO_SP_TransactionNotification;

Create  PROCEDURE "VISIONTEST".SBO_SP_TransactionNotification ( in object_type nvarchar(20), -- SBO Object Type

in transaction_type nchar(1),

-- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose

in num_of_cols_in_key int,

in list_of_key_cols_tab_del nvarchar(255),

in list_of_cols_val_tab_del nvarchar(255) ) LANGUAGE SQLSCRIPT AS  --Return values

cnt int;                      --Important: User defined additional local variable as the count of records, used in the procedure for validation

error int;                    -- Result (0 for no error)

error_message nvarchar (200); -- Error string to be displayed

begin

error := 0;

error_message := N'Ok';

----------------------------------------------------------------------

--    ADD   YOUR  CODE  HERE

IF :Transaction_Type = 'A' AND :Object_Type = '60' then

(select "VISIONTEST"."IGE1"."WhsCode" from "VISIONTEST"."IGE1"inner join

"VISIONTEST"."OIGE"  on "VISIONTEST"."IGE1"."DocEntry" = "VISIONTEST"."OIGE" ."DocEntry" where

"VISIONTEST"."IGE1"."DocEntry" = :list_of_cols_val_tab_del

and "BaseType" <> '-1' and

"VISIONTEST"."IGE1"."WhsCode"  in ('QNT','BLI','EIS','EXP','FGS','GNR','MRK','PHS' ));

         error_message := 'TEST';

      end if;

      ----------------------------------------------------------------------

--Select the return values

select

       :error,

       :error_message

FROM dummy;

end;

Dear All

i have  created an SP and appearing this error, please  see the attached screen-short and support if and one have solution.

regards

Abbas.

[former_member184146]

Hi Asad,

                    Replace your SP with below code

CREATE PROCEDURE SBO_SP_TransactionNotification

(

  in object_type nvarchar(20), -- SBO Object Type

  in transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose

  in num_of_cols_in_key int,

  in list_of_key_cols_tab_del nvarchar(255),

  in list_of_cols_val_tab_del nvarchar(255)

)

LANGUAGE SQLSCRIPT

AS

cnt int;

trip_no int;

error  int;            -- Result (0 for no error)

error_message nvarchar (200); -- Error string to be displayed

begin

error := 0;

error_message := N'Ok';

--------------------------------------------------------------------------------------------------------------------------------

---Your Code

IF :Transaction_Type = 'A' AND :Object_Type = '60' then

cnt := 0;

  select  count(*) into cnt from "VISIONTEST"."IGE1"inner join

  "VISIONTEST"."OIGE"  on "VISIONTEST"."IGE1"."DocEntry" = "VISIONTEST"."OIGE" ."DocEntry" where

  "VISIONTEST"."IGE1"."DocEntry" = :list_of_cols_val_tab_del

  and "BaseType" <> '-1' and

  "VISIONTEST"."IGE1"."WhsCode"  in ('QNT','BLI','EIS','EXP','FGS','GNR','MRK','PHS' );

  IF :cnt > 0 THEN

  error := 1;

  error_message := 'Your message';

  END IF;

END IF;

----------------------------------------------------------------

select :error, :error_message FROM dummy;

end;