Construct a SQL Server COBOL Stored Procedure

Walks you through the process of using the OpenESQL Assistant to code a SQL Server COBOL stored procedure that looks up an employee number and returns the employee number and first and last names of the employee.

Set OpenESQL Assistant Options

By default, the OpenESQL Assistant generates TSQL stored procedures. You need it to generate SQL CLR stored procedures.

  1. From the VisualStudio IDE, click Tools > Options; then expand Micro Focus Tools and click OpenESQL Assistant.
  2. On the option list in the Query group, use the drop-down list to set Type Stored Procedure to SQL CLR, and then click OK.

Create a SQL CLR Stored Procedure Query

As you construct the query, the OpenESQL Assistant generates all code necessary to complete the stored procedure program, including host variables and SQLCA declarations.

  1. In the OpenESQL Assistant, check the EMP (TEST) table listed under the SQLServerDB connection entry.
  2. On the Select Type of Query to Create dialog box, click SELECT (Singleton).
  3. Check Generate Query as a SQLCLR Stored Procedure.
  4. In the SP name field, type LookupEMP. The OpenESQL Assistant uses this name for both the generated program and the stored procedure.
  5. Click OK.
  6. On the tree view, check the A.EMPNO, A.FIRSTNME, and A.LASTNAME columns to add them to the query.
  7. In the right pane, click the Search Criteria tab.
  8. Verify that the following fields are set to the values specified:
    Column A.EMPNO
    Conditional Operator =
    Target Type Host Variable
    Target Value :EMP-EMPNO
  9. Click > (right arrow) to construct the arguments for the WHERE clause of the SELECT statement you are constructing.

Add the stored procedure to the SQLServerSP project

  1. In the Visual Studio Solution Explorer, click the SQLServerSP COBOL project name to select it.
  2. In the OpenESQL Assistant, position the mouse pointer over the toolbar; then right click and select Add SP to SQLCLR project.

    The OpenESQL Assistant adds the stored procedure to the project in the file SQLServerSP.cbl.

  3. Close the OpenESQL Assistant.

Delete the StoredProcedure1.cbl file

The program that was generated by default when you created the project, StoredProcedure1.cbl, is no longer required. Now is a good time to delete it.

  1. In the Solution Explorer, right-click the StoredProcedure1.cbl file and select Delete.
  2. Click OK to confirm deletion.
  3. Click Save All Save All.
Parent topic: Tutorial: Construct and Execute a SQL Server COBOL Stored Procedure