Return Values of Stored Procedures using Database Connectivity Toolkit

Issue Details

I am unable to get a return value when I call my stored procedure with the Database Connectivity Toolkit. What is the proper syntax for setting up the parameters and query?

Solution

The DB Tools Create Parameterized Query VI is used to call a stored procedure. The SQL query input accepts the query for the stored procedure. The parameter input is an array of clusters (name, type, direction, initial value). Where each cluster represents a column or field in the database table. To get a return value for each parameter specified, the value for direction of that parameter cluster, must be set to Return Value. The Returns cluster must be set as the first element in the Parameters array that is applied to the Parameterized Query.

Additional Information

Assuming we already have a stored procedure called storedprocedure() which is defined similarly to the following:

CREATE PROCEDURE dbo.storedprocedure
theVariable varchar(10) OUTPUT
AS
theVariable = 'return me'
RETURN

If a return value is needed from the database table, the SQL query input should be set to 
{? = call storedprocedure()} 
where the question mark declares a parameter to be defined by the parameter input.

Then, using the DB Tool Execute Query VI and DB Tools Get Parameter Value VI, the return value can be retrieved either by index or by name as shown below. 



Note that DB Tool Execute Query VI creates a RecordSet reference, so you must use the DB Tools Free Objects VI to release the RecordSet reference value and retrieve the return value as shown above.

When using an Oracle database, you may also need to ensure that a RecordSet is defined and implemented in the Stored Procedure as shown below:

...
VARIABLE_RECORDSET OUT SYS_REFCURSOR
...
OPEN VARIABLE_RECORDSET FOR
  SELECT VARIABLE_ID 
    FROM variable
    WHERE variable_id = VARIABLE_ID_OUT;