Procedure
- sunny.gupta
On the SQL Designer screen, follow these steps to work with stored procedures:
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.
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.
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;
%@>Related content
Copyright Kyvos, Inc. All rights reserved.