Table Variable as Input parameters

[former_member213277]

Hi Experts,

Can you please tell me what is the use of tables variables in procedures defined as Input parameters.

say i have created a procedure as

CREATE PROCEDURE ABC (IN ITAB TY_IN, OUT T_OUT TY_T )

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW ABC_VIEW AS

where Itab--> is Tables Variable defined as input Parameters.

When I call this procedure, I am getting an error: Parameterizing Table Variable is not supported

Thanks and Regards,

Nag

[former_member184768 · Accepted solution]

Hi Nag,

I am currently on rev 56 and tried to use the procedure with table input and output parameters. It works for me without any errors.

Can you please confirm which revision you are currently working on. Also if possible, please post your code.

Regards,

Ravi

[former_member213277 · Accepted solution]

Hi Ravi,

I am workoing on SP05 Rev52.

Can you please send sample code which explain how to pass the table type In Put variable to a procedure.

Regards,

Nagaraj

[former_member184768 · Accepted solution]

Hi Nagaraj,

Please find a sample code attached below. The base table tt_2 has 4 columns a, b, c, d.

drop type tt_tt_2;

create type tt_tt_2 as table (va smallint, vb varchar(20));

drop procedure PR_TAB_INOUT;

CREATE PROCEDURE PR_TAB_INOUT (IN ITAB tt_tt_2, OUT T_OUT tt_tt_2 )

LANGUAGE SQLSCRIPT AS

begin

  T_OUT = select va, (vb * 1.1) as vb from :ITAB;

  update tt_2 set tt_2.d = (va * 1.1) from :ITAB where tt_2.a = va;

end;

drop procedure PR_FILL_TAB;

CREATE PROCEDURE PR_FILL_TAB (OUT VT_OUT tt_tt_2)

LANGUAGE SQLSCRIPT as

begin

  VT_OUT = select a as VA, b as VB from tt_2 where b is not null;

end;

drop procedure PR_FINAL;

CREATE PROCEDURE PR_FINAL ()

LANGUAGE SQLSCRIPT as

begin

  call PR_FILL_TAB(:v_t);

  call PR_TAB_INOUT(:v_t, :v_t1);

end;

Regards,

Ravi

[former_member213277 · Accepted solution]

Thanks Ravi

Regards,

Nagaraj