sql - Error Insert Into a table variable Hana -
i'm working passing ms sql server statements hana sql statements. have variable (tab) type table , variable string (query) defined as:
hana statement
create type "tab_table_type" table ("docentry" integer, "lineid" integer, "visorder" integer, "object" nvarchar(20)); v_tab tab_table_type query := 'select [docentry],[lineid],[visorder] ,[object] [@inv_afc]';
so i'm trying convert ms sql statement hana statement :
ms sql server statement
insert @v_tab([docentry],[lineid],[visorder],[object]) exec (@query)
i wish use internal table type variable can hold resultset query!
when use sql converter sentence displays error:
--[note:errormodifier] insert statement not support exec; use exec('insert table '+ originalstatement)
--[note:stringifier] sap hana not support inserttarget
finally question : how correct hana sql statement case?
the syntax of table-type creation correct. guess trying execute query inside procedure.
inside procedure, have different options. if query partial result , want run further statements on result set of query, don't need initialize table variable @ all. assign variable resultset:
table_variable = select docentry, lineid, visorder, object inv_afc; // use table_variable further statements, example: select count(*) resultsetcount :table_variable;
if query final result, can define output variable , directly assign result set output variable. example:
create procedure "yourschema"."someprocedurename" ( in someinputvariable1 nvarchar(255), in someinputvariable2 bigint, out tablevariable "yourschema".tab_table_type) language sqlscript sql security invoker begin tablevariable = select docentry, lineid, visorder, object inv_afc; end;
when call procedure 3rd parameter automatically contain result set:
call "yourschema"."someprocedurename"('somestring', 123, ?);
Comments
Post a Comment