Sidebar

How to call a Stored Procedure with output parameters

0 votes
2.7K views
asked Feb 19, 2015 by rich-c-2789 (16,180 points)
How to call a Stored Procedure with output parameters

4 Answers

0 votes
 
Best answer

As of version .48, support for OUTPUT parameters has been added to QIE. See example in this question:

Using a Stored Procedure with Output Parameters in QIE?

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 TSQL and/or 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 (16,180 points)
+1 vote

This is an example of how to call a stored procedure with output parameters and a return value within QIE using  qie.doQuery().  This may be easier than using the JDBC example below.  This example uses the same database and stored procedure setup as the JDBC example.  The trick in using qie.doQuery is to aggregate the results to be returned with a SELECT statement.  Note: in this case we expect one row of results so we can easily add the return value and the output parameters into the same SELECT.

qie.debug("Start Nice Trick");

var detailsForId = 100;

var sp_call = "DECLARE @myreturn_value int,\n" +
" @mybusinessEntityIDout INT,\n" +
" @myloginId nvarchar(256),\n" +
" @myorganizationNode nvarchar(4000),\n" +
" @myorganizationLevel smallint,\n" +
" @mybirthDate date,\n" +
" @mymaritalStatus nchar(1),\n" +
" @mysalariedFlag Flag,\n" +
" @myvacationHours smallint,\n" +
" @myrowguid uniqueidentifier,\n" +
" @mymodifiedDate datetime\n" +

"\n" +
"EXEC @myreturn_value = [dbo].[getEmployeeDetail]\n" +
" @businessEntityIDIn = " + detailsForId + ",\n" +
" @businessEntityIDout = @mybusinessEntityIDout OUTPUT,\n" +
" @loginId = @myloginId OUTPUT,\n" +
" @organizationNode = @myorganizationNode OUTPUT,\n" +
" @organizationLevel = @myorganizationLevel OUTPUT,\n" +
" @birthDate = @mybirthDate OUTPUT,\n" +
" @MaritalStatus = @mymaritalStatus OUTPUT,\n" +
" @salariedFlag = @mysalariedFlag OUTPUT,\n" +
" @vacationHours = @myvacationHours OUTPUT,\n" +
" @rowguid = @myrowguid OUTPUT,\n" +
" @modifiedDate = @mymodifiedDate OUTPUT\n" +

"\n" +
"SELECT\n" +
" @myreturn_value as N'return_value',\n" +

" @mybusinessEntityIDout as N'businessEntityIDout',\n" +
" @myloginId as N'loginId',\n" +
" @myorganizationNode as N'organizationNode',\n" +
" @myorganizationLevel as N'organizationLevel',\n" +
" @mybirthDate as N'birthDate',\n" +
" @mymaritalStatus as N'maritalStatus',\n" +
" @mysalariedFlag as N'salariedFlag',\n" +
" @myvacationHours as N'vacationHours',\n" +
" @myrowguid as N'rowguid',\n" +
" @mymodifiedDate as N'modifiedDate'";

sp_call = qie.evaluateTemplate(sp_call);
qie.debug("sp_call = " + sp_call);

var queryResult = qie.doQuery("AdventureWorks2012",sp_call);

qie.debug("queryResult: " + queryResult);
qie.debug("return_value: " + queryResult.getNode("return_value"));
qie.debug("businessEntityIDout: " + queryResult.getNode("businessEntityIDout"));
qie.debug("loginId: " + queryResult.getNode("loginId"));
qie.debug("organizationNode: " + queryResult.getNode("organizationNode"));
qie.debug("organizationLevel: " + queryResult.getNode("organizationLevel"));
qie.debug("birthDate: " + queryResult.getNode("birthDate"));
qie.debug("maritalStatus: " + queryResult.getNode("maritalStatus"));
qie.debug("salariedFlag: " + queryResult.getNode("salariedFlag"));
qie.debug("vacationHours: " + queryResult.getNode("vacationHours"));
qie.debug("rowguid: " + queryResult.getNode("rowguid"));
qie.debug("modifiedDate: " + queryResult.getNode("modifiedDate"));

answered Feb 24, 2015 by rich-c-2789 (16,180 points)
edited Jul 18, 2019 by rich-c-2789
0 votes

Below is an example of how to call a stored procedure with output parameters and a return value within QIE using the JDBC APIs.  This example uses the MS SQL Server AdventureWorks2012 database.  See the additional answers for the definition of the stored procedure used in this example.

//STEP 1: Obtain a connection
var connection = qie.getDbConnection('AdventureWorks2012');

var statement;
try {
  //STEP 2: Create a callable statement (Note: this is not a PreparedStatement)
  qie.debug("Creating statement...");
  statement = connection.prepareCall("{? = call dbo.[getEmployeeDetail](?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

  //STEP 3: Register the return value
  statement.registerOutParameter(1, java.sql.Types.INTEGER);

  //STEP 4: Set the INPUT parameter values
  statement.setInt(2, 5);

  //STEP 5: Register the OUTPUT parameters
  statement.registerOutParameter(3, java.sql.Types.INTEGER);
  statement.registerOutParameter(4, java.sql.Types.NVARCHAR);
  statement.registerOutParameter(5, java.sql.Types.NVARCHAR);
  statement.registerOutParameter(6, java.sql.Types.SMALLINT);
  statement.registerOutParameter(7, java.sql.Types.DATE);
  statement.registerOutParameter(8, java.sql.Types.NCHAR);
  statement.registerOutParameter(9, java.sql.Types.BIT);
  statement.registerOutParameter(10, java.sql.Types.SMALLINT);
  statement.registerOutParameter(11, java.sql.Types.NVARCHAR);
  statement.registerOutParameter(12, java.sql.Types.DATE);

  //STEP 6: Execute stored procedure
  statement.execute();

  //STEP 7: Consume the "return value"
  qie.debug("Return Value: " + statement.getInt(1));

  //STEP 8: Consume the output parameters
  qie.debug("BusinessEntityID: " + statement.getInt(3));
  qie.debug("Login ID: " + statement.getString(4));
  qie.debug("OrganizationNode: " + statement.getString(5));
  qie.debug("OrganizationLevel: " + statement.getInt(6));
  qie.debug("BirthDate: " + statement.getDate(7));
  qie.debug("MaritalStatus: " + statement.getString(8));
  qie.debug("SalariedFlag: " + statement.getBoolean(9));
  qie.debug("VacationHours: " + statement.getInt(10));
  qie.debug("RowGuid: " + statement.getString(11));
  qie.debug("ModifiedDate: " + statement.getDate(12));

  //STEP 9: 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);
  }
}

For a list of the java.sql.Types used in step 5 see the java docs

Note: Note that return values are registed like output parameters.  The index used as the first parameter in the registerOutParameter() and setInt() methods are based on the order of the question marks in step 2.  

 

 

answered Feb 19, 2015 by rich-c-2789 (16,180 points)
0 votes

Here is the code for the stored procedure:

USE [AdventureWorks2012]
GO
 
CREATE PROCEDURE getEmployeeDetail
  @businessEntityIDIn INT,
  @businessEntityIDout INT OUTPUT,
  @loginId nvarchar(256) OUTPUT,

  @organizationNode nvarchar(4000) OUTPUT,

  @organizationLevel smallint OUTPUT,
  @birthDate date OUTPUT,
  @maritalStatus nchar(1) OUTPUT,
  @salariedFlag Flag OUTPUT,
  @vacationHours smallint OUTPUT,
  @rowguid uniqueidentifier OUTPUT,
  @modifiedDate datetime OUTPUT
AS
BEGIN
  SELECT
    @businessEntityIDout = [BusinessEntityID],
    @loginId = [LoginID],
    @organizationNode = [OrganizationNode].ToString(),
    @organizationLevel = [OrganizationLevel],
    @birthDate = [BirthDate],
    @maritalStatus = [MaritalStatus],
    @salariedFlag = [SalariedFlag],
    @vacationHours = [VacationHours],
    @rowguid = [rowguid],
    @modifiedDate = [ModifiedDate]
  FROM HumanResources.Employee
  WHERE BusinessEntityID = @BusinessEntityIDIn

  RETURN 42
END

Here is how to call it in SQL Server:

USE [AdventureWorks2012]
GO

DECLARE @return_value int,
  @mybusinessEntityIDout INT,
  @myloginId nvarchar(256),
  @myorganizationNode nvarchar(4000),
  @myorganizationLevel smallint,
  @mybirthDate date,
  @mymaritalStatus nchar(1),
  @mysalariedFlag Flag,
  @myvacationHours smallint,
  @myrowguid uniqueidentifier,
  @mymodifiedDate datetime

EXEC @return_value = [dbo].[getEmployeeDetail]
  @businessEntityIDIn = 100,
  @businessEntityIDout = @mybusinessEntityIDout OUTPUT,
  @loginId = @myloginId OUTPUT,
  @organizationNode = @myorganizationNode OUTPUT,
  @organizationLevel = @myorganizationLevel OUTPUT,
  @birthDate = @mybirthDate OUTPUT,
  @MaritalStatus = @mymaritalStatus OUTPUT,
  @salariedFlag = @mysalariedFlag OUTPUT,
  @vacationHours = @myvacationHours OUTPUT,
  @rowguid = @myrowguid OUTPUT,
  @modifiedDate = @mymodifiedDate OUTPUT

SELECT
  @mybusinessEntityIDout as N'@mybusinessEntityIDout',
  @myloginId as N'@myloginId',
  @myorganizationNode as N'@myorganizationNode',
  @myorganizationLevel as N'@myorganizationLevel',
  @mybirthDate as N'@mybirthDate',
  @mymaritalStatus as N'@mymaritalStatus',
  @mysalariedFlag as N'@mysalariedFlag',
  @myvacationHours as N'@myvacationHours',
  @myrowguid as N'@myrowguid',
 @mymodifiedDate as N'@mymodifiedDate'


SELECT 'Return Value' = @return_value

GO

 

 

Note: There are three data types defined in the HumanResources.Employee table that need special attention.

  • OrganizationNode is defined with a "hierarchyid" data type. 

See MSDN Library for more on the hiearchyid.  To handle this type the .ToString() was called in the stored procedure.  The java code is then able to consume it as a String

  • SalariedFlag is defined as a Flag(bit).  

In this case Flag is an Alias Data Type for bit.  bit types can be consumed in Java as a boolean.

  • rowguid is defined as a uniqueidentifier.  

Which can be consumed as a String in Java

The point is that you may need to find the right mapping from the data type used in the database vs the type used in Java.  This is a good resource to Understand the JDBC Driver Data Types.  It also some a handy tables that show how they are mapped.  If needed, the conversion to a valid type can be done in the stored procedure.  

answered Feb 19, 2015 by rich-c-2789 (16,180 points)
...