Tutorial: SQL CLR Stored Procedures - Call a Stored Procedure Defined in an SPD File using Group Variables

Walks you through the process of invoking a native COBOL program using JCL, calling a SQL Server SQL CLR stored procedure passing group structured host variables, and returning a group variable back to the calling COBOL native program.

The SQL CLR stored procedure used in this tutorial was generated using the Generate SPD File tool.

Prerequisites

Before attempting this tutorial, you must first work through Tutorial: Prepare the SQL CLR Environment.
If you have not already done so, review the Assumptions and Before you begin a tutorial sections in the Tutorials: HCO for SQL Server (HCOSS) topic to ensure your Enterprise Developer environment is set up properly.

Demonstration solution - SQLCLRTutorialSPCall

The SQLCLRTutorialSPCall solution provided for this tutorial includes components of JCL, native COBOL, and SQL CLR stored procedure code. First, a native COBOL program is invoked via JCL. The invoked program calls a SQL Server SQL CLR stored procedure that returns data to the calling program. You build the stored procedure into a DLL that is deployed to the sample SQLCLR_Test SQL Server database.

The solution contains three projects:

SPCall Project

A COBOL/JCL project that contains a native COBOL program that initiates the first call to a SQL CLR stored procedure, and the JCL that invokes it the program.

SPCall.jcl

JCL script that invokes SPCall.cbl:

 RUN PROG (SPCALL) PLAN (SPCALL) LIB ('MY.DBRMLIB')

SPCall.cbl

Native COBOL program invoked by SPCall.jcl, this program calls the GETHIRE program in the SQLCLRTutorial project.

exec sql
     call "GETHIRE" (:EMPID INOUT, :FIRSTNAME OUT, :LASTNAME OUT, :HIREDATE OUT, :SQLXCODE OUT)
end-exec

SQLCLRTutorial Project

Created from the SQL Server Database project template, the SQLCLRTutorial project contains the following:

GETHIRE.cbl: A native COBOL program that runs the SQL Server stored procedure code. SQL Server has meta-data that identifies and loads the assembly that contains the stored procedure.
SQLCLRStoredProc.spd: A file that contains stored procedure definition code.
SQLCLRStoredProc.mssp.cbl: SQLCLRStoredProc.mssp.cbl contains stored procedure code. At run time, SQL Server meta-data identifies the assembly containing this stored procedure code, and then loads the assembly into the SQL Server database. The call from GETHIRE.cbl to the stored procedure is found in this loaded assembly.

SQLCLRTutorial.Publish Project

Also created by default from the SQL Server Database project template, the SQLCLRTutorial.Publish project does not contain any application code. Its purpose is to create the deployment package for the solution and to deploy it. The publishing process creates the stored procedure that calls the into the DLL that SQL Server executes in a SQL CLR run-time environment. The SQL CLR run time works with the COBOL run-time. Publishing includes deploying both of these run-times.

Sequence

To complete this tutorial, progress through these topics in the order presented here. The bottom of each topic provides Next topic and Previous topic navigational links to help you proceed in the proper sequence: