The CALL keyword is an ANSI/ISO/IEC SQL keyword. Also referred to as the procedure CALL escape sequence. This is also used by JDBC to provide a common way of calling stored procedures for different database vendors. However, even though it is part of the standard, check with your database vendors reference documentation for support.
The EXEC keyword is database vendor specific. It may not be supported by all database vendors or may have variants in syntax. In some database systems EXEC is the short form of the EXECUTE keyword. Some database vendors provide other syntax for calling stored procedures that will not be covered here. For the rest of this answer I will focus on using Microsoft SQL Server as the database vendor.
Both can be used in QIE. However, there are several reason you might choose one over the other. If you work with several different database vendors you might prefer to use CALL. On the other hand, if you use only one database vendor, EXEC might be easier to use. In the case of Microsoft SQL Server it is easier to debug a EXEC statement. Since you can run it in SSMS to validate the syntax and results before embedding it into your QIE script.
Here is a simple example for using each in QIE to call the same stored procedure:
Using CALL in QIE:
statement = connection.prepareCall("{CALL [dbo].[getEmployeeDetailAsResultSet](?)}");
vs.
Using EXEC in QIE:
statement = connection.prepareCall("EXEC [dbo].[getEmployeeDetailAsResultSet] @businessEntityIDIn = ?");
Both do exactly the same thing. They execute the getEmployeeDetailAsResultSet stored procedure. Both pass in the 'businessEntityIDIn' parameter defined in the stored procedure. Which returns the record that matches the value passed in.
The CALL keyword or escape sequence in Microsoft SQL Server uses this syntax:
{? = CALL proc_name(?,...)} or {CALL proc_name(?,...)} depending on whether it has a return parameter.
Parameters are passed by the order defined in the stored procedure. To learn more about using CALL escape sequence for Microsoft SQL Server see the 'Stored Procedure Calls' section in this reference: Using SQL Escape Sequences
The EXEC keyword in Microsoft SQL Server uses this syntax:
EXEC proc_name ?,...
Parameters can be passed by the order defined in the stored procedure or by name. See this reference to learn more about how to specify parameters: Specify Parameters
To learn more about using the EXEC keyword for Microsoft SQL Server see: Execute a Stored Procedure
The above example is based on the stored procedure below. The getEmployeeDetailAsResultSet stored procedure takes just one INPUT parameter, no OUTPUT parameters, no OUTPUT cursors, and returns just one result set.
NOTE: Understanding the INPUT, OUTPUT, CURSORS, and RESULT SETS implemented in a stored procedure will help you understand how to call it and consume the outout, cursors, and results. To learn more see: Using Statements with Stored Procedures
USE [AdventureWorks2012]
GO
/****** Object: StoredProcedure [dbo].[getEmployeeDetailAsResultSet] Script Date: 7/22/2019 11:52:52 AM ******/
DROP PROCEDURE [dbo].[getEmployeeDetailAsResultSet]
GO
/****** Object: StoredProcedure [dbo].[getEmployeeDetailAsResultSet] Script Date: 7/22/2019 11:52:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getEmployeeDetailAsResultSet]
@businessEntityIDIn INT
AS
BEGIN
SELECT
[BusinessEntityID],
[LoginID],
[OrganizationNode].ToString() as OrganizationStructure,
[OrganizationLevel],
[BirthDate],
[MaritalStatus],
[SalariedFlag],
[VacationHours],
[rowguid],
[ModifiedDate]
FROM HumanResources.Employee
WHERE BusinessEntityID = @BusinessEntityIDIn
END
GO
Here is the full QIE script:
//STEP 1: Obtain a connection
var connection = qie.getDbConnection('AdventureWorks2012');
var statement;
try {
//STEP 2: Create a callable statement
qie.debug("Creating statement...");
statement = connection.prepareCall("{CALL [dbo].[getEmployeeDetailAsResultSet](?)}");
// statement = connection.prepareCall("EXEC [dbo].[getEmployeeDetailAsResultSet] @businessEntityIDIn = ?");
//STEP 3: Set the INPUT parameter values
statement.setInt(1, 5);
//STEP 4: Execute stored procedure
var results = statement.executeQuery();
//STEP 5: Consume the "results"
while (results.next()) {
qie.debug("BusinessEntityID: " + results.getString('BusinessEntityID'));
qie.debug("LoginID: " + results.getString('LoginID'));
qie.debug("OrganizationStructure: " + results.getString('OrganizationStructure'));
qie.debug("OrganizationLevel: " + results.getString('OrganizationLevel'));
qie.debug("BirthDate: " + results.getString('BirthDate'));
qie.debug("MaritalStatus: " + results.getString('MaritalStatus'));
qie.debug("SalariedFlag: " + results.getString('SalariedFlag'));
qie.debug("VacationHours: " + results.getString('VacationHours'));
qie.debug("rowguid: " + results.getString('rowguid'));
qie.debug("ModifiedDate: " + results.getString('ModifiedDate'));
}
//STEP 6: Clean-up environment
statement.close();
connection.close();
} catch(e) {
qie.debug(e);
} finally {
try{
if (statement !== null) {
statement.close();
}
} catch (e) {
qie.debug(e);
}
try{
if (connection !== null) {
connection.close();
}
} catch (e) {
qie.debug(e);
}
}
