The following example shows a macro with a DTW_SQL function definition that calls an SQL stored procedure. It has three parameters of different data types. The DTW_SQL language environment converts the character string values in each parameter to the correct internal format and passes each parameter by reference to the SQL stored procedure. When the SQL stored procedure completes processing, the updated internal representation is converted to a character string and placed in the corresponding parameter.
%{*********************************************************** ********************DEFINE BLOCK****************************
************************************************************%}
DEFINE {
MACRO_NAME = "TEST ALL TYPES"
DTW_HTML_TABLE = "YES"
Procedure = "NDLIB.TESTTYPE"
parm1 = "1" %{SMALLINT %}
parm2 = "11" %{INT %}
parm3 = "1.1" %{DECIMAL (2,1) %}
%}
%FUNCTION(DTW_SQL) CRTPROC(){
CREATE PROCEDURE $(Procedure)
( INOUT SMALLINT,
INOUT INT,
INOUT DECIMAL(2,1))
EXTERNAL NAME $(Procedure) LANGUAGE C SIMPLE CALL
%MESSAGE{
default : "$(DTW_DEFAULT_MESSAGE) : continuing.<br>": continue
%}
%}
%FUNCTION(DTW_SQL) myProc
(INOUT SMALLINT parm1,
INOUT INT parm2,
INOUT DECIMAL(2,1) parm3){
CALL $(Procedure)
%}
%HTML(REPORT) {
<HEAD>
<TITLE>Net.Data : SQL Stored Procedure: Example '$(MACRO_NAME)'. <?TITLE>
</HEAD>
<BODY BGCOLOR="#BBFFFF" TEXT="#000000" LINK="#000000">
<p><p>
Calling the function to create the stored procedure.
<p><p>
@CRTPROC()
<hr>
<h2>
Values of the INOUT parameters
prior to calling the stored procedure:<p>
</h2>
<b>parm1 (SMALLINT)</b><br>
$(parm1)<p>
<b>parm2 (INT)</b><br>
$(parm2)<p>
<b>parm3 (DECIMAL)</b><br>
$(parm3)<p>
<p>
<hr>
<h2>
Calling the function that executes the stored procedure.
</h2>
<p><p>
@myProc(parm1,parm2,parm3)
<hr>
<h2>
Values of the INOUT parameters after
calling the stored procedure:<p>
</h2>
<b>parm1 (SMALLINT)</b><br>
$(parm1)<p>
<b>parm2 (INT)</b><br>
$(parm2)<p>
<b>parm3 (DECIMAL)</b><br>
$(parm3)<p>
</body>
%}