Procedure

On the SQL Designer screen,

Select the Procedure tab to view the list of stored procedures in your database connection. Drag and drop the procedure on the Procedure tab and select the required parameters.

Figure 4: SQL Designer: Procedure Tab

To set procedure and provide value of parameters if any:

• Click Procedure tab to bring it in front.

• Click Procedure from the box listing database object types. Procedures are listed in Entities

• Double-click an entity (procedure) to select it. Procedure Name appears in Procedure Name Parameters in that procedure appears in the Parameter list.

• For each entry (parameter), specify a constant value or parameter name

You can also execute the stored procedure through the Edit tab by calling the Stored Procedures followed by parameters.

Click Edit tab on the SQL Designer screen. The below screen appears.

Figure 5: SQL Designer: Edit Tab

Dynamic SQL Query

Using parameters:

To include a parameter in the SQL Query, specify a parameter enclosed within <% and %>.

Example: To dynamically decide where clause:

SELECT * FROM EmpTbl WHERE prmManufacturer = ‘<%PrmWhrCls%>’;

Using javascript code block:

Use javascript code block enclosed by <@% and %@> to create a dynamic SQL Query.  After execution, the code block should return a string that will be replaced in the SQL query.

You can use following in the code block:

• if else

• parameter value and parameter data type

Syntax of code block:

<@%
[Executable block, which returns a string]
%@>

Syntax to use parameter in code block:

params(“parameterName”).getAttribute()

Attributes: “Value” to get value of the parameter and “DataType” to get data type of the parameter.

Examples

Example 1. Check if parameter “prmEmpNo” exists. If it does and its value is not blank, return the value, else return nothing.

Select * from emp where 1=1
<@%
if(params(“prmEmpNo”) != null &&
params(“prmEmpNo”).getValue() != “”)
{
return ” AND empno in <%prmEmpNo%>”;
}
else
{
return “”;
}
%@>

Example 2. Check if parameter “prmSelectTable” exists. If it doesn’t, return table name as “emp” else (if it exists) value of the parameter to be used as table name.

Select * from
<@%
if(params(“prmSelectTable”) == null)
{
return “emp”;
else
return params(“prmSelectTable”).getValue();
}
%@>

Example 3. Construct WHERE clause dynamically (for LOCATIONTYPEID and REGNID fields).

select LOCTIONID, LOCTIONNAME,LOCTYPEID,REGNID from LOCATIONMASTER where 1=1
<@%
var v_Str;
v_Str = ”;
var v_Str1;
var v_Str2;
var v_Str1=”;
var v_Str2=”;

if(params(“prmloctype”) != null &&
params(“prmloctype”).getValue() != “”)
{

v_Str1 = ” AND LOCATIONTYPEID in (<%prmloctype%>)”;
}
else
{
v_Str1 = “”;
}

if(params(“prmRegion”) != null &&
params(“prmRegion”).getValue() != “”)
{
v_Str2 = ” AND REGNID = <%prmRegion%> “;
}
else
{
v_Str2 = “”;
}

v_Str = v_Str1 +v_Str2;
return v_Str;
%@>