Sidebar

What is the difference between the CALL keyword and the EXEC keyword to call a stored procedure?

0 votes
5.2K views
asked Jul 22, 2019 by rich-c-2789 (15,580 points)
edited Jul 22, 2019 by rich-c-2789
I have seen both CALL and EXEC keywords used to call a stored procedure. What is the difference? How do I use them in QIE?

3 Answers

+1 vote
 
Best answer

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);
   }
}
answered Jul 22, 2019 by rich-c-2789 (15,580 points)
edited 1 day ago by rich-c-2789
0 votes
Also see these topics:
Topic Description
Using a Stored Procedure with No Parameters Describes how to use the JDBC driver to run stored procedures that contain no input or output parameters.
Using a Stored Procedure with Input Parameters Describes how to use the JDBC driver to run stored procedures that contain input parameters.
Using a Stored Procedure with Output Parameters Describes how to use the JDBC driver to run stored procedures that contain output parameters.
Using a Stored Procedure with a Return Status Describes how to use the JDBC driver to run stored procedures that contain return status values.
Using a Stored Procedure with an Update Count Describes how to use the JDBC driver to run stored procedures that return update counts.
answered Jul 22, 2019 by rich-c-2789 (15,580 points)
0 votes

As of version .48, support for OUTPUT parameters has been added to QIE.  Version .51 added support for calling stored procedures that do not contain SET NOCOUNT ON, and added support for calling stored procedures that return multiple result sets. 

Additional examples can be found in this question:

How to call a stored procedures in QIE?

For reference the previous answers in this question use the JDBC api's to call stored procedures for MSSQL.  The examples in the questions above use new pQuery methods in the Code Wizard.  The examples in this question are still supported but the pQuery methods in the Code Wizard are easier to use.  Examples can be found in the linked questions above. 

answered Jul 20, 2021 by rich-c-2789 (15,580 points)
...