Tutorial: Call a Published Stored Procedure

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
• Tutorial: Code a SQL CLR Stored Procedure using OpenESQL Assistant
• Tutorial: Publish, Debug, and Execute a Stored Procedure

Phase 1: Create a Visual Studio Project

You need to create a Visual Studio project in which to code a program that calls your published stored procedure.

1. From the Visual Studio IDE, click File > New > Project.
2. Under Installed Templates, click COBOL > Managed > Console Application.
3. From the drop-down field above the list of templates, select .NET Framework 4 or .NET Framework 4.5.
4. In the Name field, type SQLCLRTutorialCall.
5. In the Location field, specify a directory in which to store the project; then click OK.

   You now have a new solution containing one project.

Phase 2: Create a 32-bit Solution Platform

Because Visual Studio runs in 32-bit, and the connection you've created using SQL Server 2012 runs in 64-bit, you need to add a 32-bit solution platform before you can execute your stored procedure.

1. From the Solution Explorer, right-click the Solution name; then select Configuration Manager.
2. From the Active solution platform drop-down list, do one of the following:
   • If x86 is an option, select it.
   • If x86 is not an option:
     1. Select <New...>.
     2. From the Type or select the new platform drop-down list, select x86; then click OK.
3. From the Configuration Manager, click Close.

Phase 3: Set Project Properties

1. From the Solution Explorer, double-click Properties under your SQLCLRTutorialCall project.
2. On the SQL tab, select OpenESQL from the ESQL Preprocessor drop-down list; then click Add.
3. On the Available Directives list, click DBMAN; then click OK.
4. From the Value drop-down, select ADO.
5. Click Save, and close the Properties window.

Phase 4: Code a COBOL Program

You now code a COBOL program to call your stored procedure.

1. If Program1.cbl is not open in the code editor, double-click it from the Solution Explorer. If it is open, click its tab to bring it into focus.
2. Replace all of the code in the program with the following code:

          program-id. Program1 as "SQLCLRtutorialCall.Program1".
   
          data division.
           working-storage section.
           exec sql include sqlca end-exec.
   
           01 empid       PIC X(6).  *>string.
           01 lastname    PIC X(50). *>string.
           01 firstname   PIC X(50). *>string.
          
           01 connectString  string.
           01 spReturnCode binary-long.
          
           procedure division.
               exec sql connect to "SQLCLRtutorial" end-exec
       
               if sqlcode <> 0
                  display "CONNECT FAILED"
               end-if
           
               set empid to "000020"
               exec sql
                    :spReturnCode = call "SQLCLRtutorial" (:empid INOUT, :lastname OUT, :firstname OUT)
               end-exec
              
               if sqlcode <> 0
                    display "Call FAILED"
               else
                    display "User = " firstname " " lastname
               end-if
          
               exec sql disconnect all end-exec.
               goback.
              
           end program Program1.

   Note: You could also use the OpenESQL Assistant to generate the CALL statement from the Auxiliary Code tab and insert it into the program rather than coding it manually as done here.
3. Save the program.

Phase 5: Run the COBOL Program

1. From the Program1.cbl [Code] window, insert a breakpoint at the goback. statement.
2. Press F5 to run the program.
   When the debugger hits the breakpoint, you should see the following in the Output window as a result of calling the stored procedure:

   User = MICHAEL THOMPSON

3. Click File > Save All (optional).
4. Exit Visual Studio.

Optionally continue to Tutorial: Prepare to Debug in a Development Environment. Otherwise, this concludes this series of SQL CLR Integration tutorials.