On the SQL Designer screen, follow these steps to work with stored procedures:

1. Accessing Procedures:

   • Select the Procedure tab to view a list of stored procedures available in your database connection.

   • Drag and drop the required procedure onto the Procedure tab.

   • Select and configure the necessary parameters for the procedure.

2. Setting Procedure and Parameters:

   • Click the Procedure tab to bring it to the foreground.

   • Select Procedure from the list of database object types. The procedures are listed under Entities.

   • Double-click the desired procedure. The procedure name appears in the Procedure Name field, and its parameters are listed in the Parameter List.

   • For each parameter, specify a constant value or parameter name.

3. Executing a Procedure via the Edit Tab:

   • Open the Edit tab and call the stored procedure, passing the required parameters.

Dynamic SQL Queries

Using Parameters

To include a parameter in an SQL query, enclose it within <% and %>.

Example: Dynamically define a WHERE clause:

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

Using JavaScript Code Blocks

Create dynamic SQL queries using a JavaScript code block enclosed within <@% and %@>. After execution, the block returns a string to be replaced in the SQL query.

Code Block Syntax:

<@% [Executable block that returns a string] %@>

Access Parameter Attributes:

params("parameterName").getAttribute("Value");  // To get parameter value  
params("parameterName").getAttribute("DataType");  // To get parameter data type  

Examples

Example 1: Conditionally Include a Parameter
Check if prmEmpNo exists and is not blank.

SELECT * FROM emp WHERE 1=1  
<@%
if (params("prmEmpNo") != null && params("prmEmpNo").getValue() != "") {
    return " AND empno in <%prmEmpNo%>";
} else {
    return "";
}
%@>

Example 2: Dynamically Set Table Name
Use a default table name (emp) if prmSelectTable does not exist.

SELECT * FROM  
<@%
if (params("prmSelectTable") == null) {
    return "emp";
} else {
    return params("prmSelectTable").getValue();
}
%@>

Example 3: Construct a WHERE Clause Dynamically
Build WHERE conditions for LOCATIONTYPEID and REGNID.

SELECT LOCATIONID, LOCATIONNAME, LOCTYPEID, REGNID FROM LOCATIONMASTER WHERE 1=1  
<@%
var v_Str = "";
var v_Str1 = "";
var v_Str2 = "";
if (params("prmloctype") != null && params("prmloctype").getValue() != "") {
    v_Str1 = " AND LOCATIONTYPEID in (<%prmloctype%>)";
}
if (params("prmRegion") != null && params("prmRegion").getValue() != "") {
    v_Str2 = " AND REGNID = <%prmRegion%>";
}
v_Str = v_Str1 + v_Str2;
return v_Str;
%@>