Tutorial: Code a SQL CLR Stored Procedure using OpenESQL Assistant

Requirements

Before attempting this tutorial, you must complete the following tutorials in the order listed:

• Tutorial: Enable SQL CLR Integration
• Tutorial: Create a Sample Database
• Tutorial: Create and Configure a Database Project
• Tutorial: Create an ADO.NET Connection

Phase 1: Create a SQL CLR Stored Procedure Query

1. In the OpenESQL Assistant, check the EMP (TEST) table listed under the SQLCLRTutorial 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 SQLCLRTutorial. 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.FIRSTNAME, and A.LASTNAME columns to add them to the query.
7. 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 building.

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

Phase 2: Add the Stored Procedure to the Project

1. In the Solution Explorer, click the SQLCLRTutorial COBOL project name to ensure that our stored procedure is added to the correct project.
2. Right-click over the toolbar in the OpenESQL Assistant, and select Add SP to SQLCLR project.

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

3. Click Save.

This concludes this tutorial. Please continue with Tutorial: Publish, Debug, and Execute a Stored Procedure.