SQL Execute Immediate Command with Input-Output Parameters on HANA Database

SQLScript Execute Immediate command enables to pass input and output parameters to SQL commands running on SAP HANA database for developers with Using and Into clause.
In this HANA database tutorial, I want to show how SQL programmers can pass input parameters to Execute Immediate SQLScript command which enables dynamic SQL programming for developers. Additionally, using the same case database developers will see how to read output parameter values provided to SQLScript Execute Immediate command.

For this SQLScript tutorial demonstration of Execute Immediate command with input and output parameters by using "INTO" and "USING" clauses, I create following table on a development SAP HANA database and populate it with sample data by running INSERT commands


create column table TCodes (

 id int generated by default as identity(start with 1 increment by 1) not null,

 tcode varchar(20),

 ttext varchar(36)

)

Code

I create transaction codes sample column table in my development schema named TCodes.
HANA database table TCodes, has 3 columns. First column is an identity column which is automatically generated for each row inserted into the table and starts from 1 and increases by 1 with each record insert. Second column is transaction code column, tcode. And the last column ttext, is for description of the transaction code.

After our sample column table is created on the HANA database, we can now create some sample data for this tutorial.


insert into TCodes (tcode, ttext) values ('SE78', 'Administration of Form Graphics');

insert into TCodes (tcode, ttext) values ('SO10', 'Standard Text');

insert into TCodes (tcode, ttext) values ('SICF', 'HTTP Service Hierarchy Maintenance');

insert into TCodes (tcode, ttext) values ('SQVI', 'QuickViewer Tool');

insert into TCodes (tcode, ttext) values ('SQ01', 'Query from User Group');

insert into TCodes (tcode, ttext) values ('WE30', 'Develop IDoc Types');

insert into TCodes (tcode, ttext) values ('WE02', 'IDoc List');

insert into TCodes (tcode, ttext) values ('SM30', 'Maintain Table Views');

Code

Here is our sample column table

SAP HANA database table with identity column

Now, assume that SQLScript programmer wants to insert a new row by using parameters for tcode and transaction code text. Also assume that SQL developer requires the ID value of the new row.
The SQLScript code for solving this requirement is as follows:


declare sqlcommand nvarchar(4000);

declare tcode varchar(20) = 'SE11';

declare ttext varchar(36) = 'ABAP Dictionary Maintenance';

declare id int;



insert into TCodes (tcode, ttext) values (:tcode,:ttext);

select current_identity_value() into "ID" from dummy;



select * from TCodes where id = :id;

Code

Here is the output. Please note that I have two input parameters here. And one output parameter which I had its value by using SQLScript current_identity_value() function.

SQLScript Insert command into SAP HANA database table with identity column

This time assume that HANA database developer want to parameterize the SQLScript code

In following SQL code, the INSERT statement is converted into a parameterized SQLScript command.
The tcode and ttext parameters (respectively :tcode and :ttext) are replaced with "?,?"
HANA database developers can pass values to Execute Immediate SQL command with USING clause. Right after USING clause corresponding parameter values are provided.


declare sqlcommand nvarchar(4000);

declare tcode varchar(20) = 'SP02';

declare ttext varchar(36) = 'List of Spool Requests';

declare id int;



sqlcommand := 'insert into TCodes (tcode, ttext) values (?,?);';

EXECUTE IMMEDIATE sqlcommand USING :tcode, :ttext;



sqlcommand := 'select current_identity_value() from dummy;';

EXECUTE IMMEDIATE sqlcommand INTO "ID";



select * from TCodes where id = :id;

Code

To run HANA SQL command read output values from "Execute Immediate", "INTO" clause is used.
The values in the SELECT field list are mapped into output parameters which are defined right after INTO clause.

SAP HANA Execute Immediate SQLScript command with input-output parameters

As I tried to show with a SQL example in this HANA database tutorial, SQLScript developers can run "Execute Immediate" command with input parameters by using "USING" clause and with output parameters by using "INTO" clause.