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

1. Accessing Procedures:

   • Select the Procedure tab to view

...

1. • a list of stored procedures available in your database connection.

   • Drag and drop the required procedure

...

1. • onto the Procedure tab.

   • Select and

...

1. • configure the

...

1. • necessary parameters for the procedure.

...

Figure 4: SQL Designer: Procedure Tab

...

1. Setting Procedure and Parameters:

   • Click the Procedure tab to bring it

...

1. • to the foreground.

...

1. • Select Procedure from the

...

1. • list of database object types.

...

1. • The procedures are listed

...

1. • under Entities.

   • Double-click

...

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

   • For each

...

1. • parameter

...

1. • , 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

...

1. • .

2. 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 the an SQL Query, specify a parameter enclosed query, enclose it within <% and %>.

Example: To dynamically decide where 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 by within <@% and %@> to create a dynamic SQL Query.   After execution, the code block should return returns a string that will to 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 = “”;
}

...

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;
%@>