Tutorial: Create, Run, and Call a SQL CLR Stored Procedure

Takes you through the process of creating a connection, coding a stored procedure using OpenESQL Assistant, and running the application that calls the 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

Create an ADO.NET Connection

Catalog an ADO.NET database connection and connect to it using the OpenESQL Assistant.

Start OpenESQL Assistant
  • In Visual Studio, click View > Micro Focus SQL Tools > OpenESQL Assistant.
Catalog an ADO.NET Connection
  1. From the tool bar in the OpenESQL Assistant, click the ADO.NET Connection Editor icon Create New Query.
  2. Click Settings Menu icon > Save > All framework/availability combinations; then do one of the following:
    • If All framework/availability combinations is checked, proceed to the next step.
    • If All framework/availability combinations is not checked, click it to check it; then proceed to the next step.
  3. Click Settings Menu icon > Framework and ensure that 4.0, 4.5, 4.6 is checked.
  4. In the bottom left corner of the ADO.NET Connection Editor, click Add to create a new database connection; then click Next.
  5. In the Data Source Name field, type SQLCLRTutorial; then click Next.
  6. On the .NET Data Providers page, select SqlClient Data Provider.
  7. Click Next to progress to the Provider Connection Details page.
  8. In the Value field that corresponds to Data Source, type . (dot) to specify your local SQL Server instance.
  9. In the Value field that corresponds to Initial Catalog, type SQLCLR_Test, which is the name of your SQL Server database.
  10. To enable Windows authentication for your SQL Server instance, change the value for the Integrated Security key to True.
  11. Click Next, and then Finish.
  12. After the new connection appears on the main window of the ADO.NET Connection Editor, click Test to verify that the connection works.
  13. Click OK to clear the message.
  14. Click the x icon in the upper right corner of the ADO.NET Connection Editor main window to close it.
Connect to your SQL Server Instance
  1. On the OpenESQL Assistant, uncheck all listed data source names, if any.
  2. From the tool bar, click the Refresh list of data sources icon Create New Query.
  3. Check SQLCLRTutorial to make the connection.

Code a SQL CLR Stored Procedure using OpenESQL Assistant

Use the OpenESQL Assistant create a SQL CLR stored procedure that looks up an employee number and returns the employee number and first and last names of the employee.

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.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 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.

Add the Stored Procedure to the Project
  1. In the Solution Explorer, click the SQLCLRTutorial 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 SQLCLRTutorial.cbl.

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

  3. In the Solution Explorer, right-click the StoredProcedure1.cbl file and select Delete.
  4. Click OK to confirm deletion.
  5. Click Save All (Save All).

Publish, Debug, and Execute a Stored Procedure

This takes you through the process of publishing, debugging, and executing the stored procedure, using the SQL Server Object Explorer in Visual Studio.

Note: When connecting to your SQL Server, if Microsoft SQL Server prompts you with an Attach Security Warning, please click Attach to clear the prompt.
Publish Your Stored Procedure to SQL Server
  1. In the Solution Explorer, right-click the SQLCLRTutorial.Publish project; then select Publish.

    Enterprise Developer builds the project and then invokes the Publish Database dialog box.

  2. On the Publish Database dialog box, click Edit.
  3. On the History tab under Recent Connections, select SQLCLR_Test; then click OK.
  4. On the Publish Database dialog box, click Publish.

    When the publishing process is complete, the Data Tools Operations window shows a status of Publish Completed Successfully.

Debug Your Stored Procedure
Now that your stored procedure is available for use, you can debug it from Visual Studio.
  1. In Visual Studio, start the SQL Server Object Explorer.
  2. On the SQL Server Object Explorer, click Add SQL Server (Add SQL Server).
  3. On the History tab under Recent Connections, select SQLCLR_Test; then click Connect.
  4. On the SQL Server Object Explorer, right-click your SQL Server instance, represented by a dot followed by your local server information in parentheses, and check Application Debugging and Allow SQL/CLR Debugging. If a prompt appears, click Yes to enable SQL CLR debugging.
  5. On the SQL Server Object Explorer, expand the entry for your local SQL Server.
  6. Expand Databases > SQLCLR_Test > Programmability > Stored Procedures.
  7. When the SQL Server Object Explorer has refreshed, right-click the dbo.SQLCLRTutorial stored procedure and select Debug Procedure.
  8. In the Value field for the @lkEMPNO name, type 000020.
  9. Check the box in the Null column for the @lkFIRSTNME and @lkLASTNAME names, and then click OK.

    Visual Studio connects to your SQL Server instance, creates a test SQL script, calls your stored procedure, and runs the debugger with your cursor on the USE [SQLCLR_Test] statement of the test SQL script.

  10. Use the Visual Studio debugger to step through the lines of code in your stored procedure, examining variables and values, etc.

    The Data Tools window on the Results tab displays information returned from the debugger.

  11. To exit the debugger, click Debug > Stop Debugging.
Execute Your Stored Procedure
You can execute the stored procedure providing by providing the required input values, and see the output that results from the stored procedure call.
  1. From the SQL Server Object Explorer, right-click the dbo.SQLCLRTutorial stored procedure, and select Execute Procedure.
  2. If not already set, type 000020 into the Value field for the @lkEMPNO name, and check Null for the other two names.
  3. Click OK to execute the stored procedure.
  4. View the results on the Results tab:
    Execute Stored Procedure Results

Call a Published Stored Procedure

This section takes you through the process of calling your published stored procedure from a COBOL program.

Note: When connecting to your SQL Server, if Microsoft SQL Server prompts you with an Attach Security Warning, please click Attach to clear the prompt.
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. In Visual Studio, click File > New > Project.
  2. In the New Project dialog box, expand Installed > COBOL.
  3. Select Database.
  4. In the center pane, select SQL Server Database Project.
  5. From the drop-down field above the list of templates, select the appropriate .NET Framework, including version 4 or later.
  6. In the Name field, type SQLCLRTutorialCall.
  7. In the Location field, specify a directory in which to store the project; then click OK.
  8. When prompted to save the current solution, click Yes.
  9. If prompted to save one or more SQLQuery SQL files, click Cancel as these files are not required.

    Visual Studio saves and closes the current solution and opens the new solution containing the SQLCLRTutorialCall project.

Create a 32-bit Solution Platform
Because Visual Studio runs in 32-bit, and the connection you've created using SQL Server 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. On the Configuration Manager, click Close.
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.
  3. Click Add.
  4. On the Available Directives list, click DBMAN; then click OK.
  5. Repeat steps 3 and 4, but this time add the BEHAVIOR directive.
    Note: The default value for both DBMAN (ADO) and BEHAVIOR (OPTIMIZE) are correct, so you don't need to make any value changes.
  6. Click Save (Save); then close the Properties window.
Code a COBOL Program
You now code a COBOL program to call your stored procedure.
  1. If Program1.cbl is not open in the COBOL 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. Click Save (Save) to save Program1.cbl.
Run the COBOL Program
  1. In the COBOL editor, insert a breakpoint at the goback statement.
  2. Press F5 to run the program in the debugger.
    When the debugger hits the breakpoint, you should see the following in a generated console window as a result of calling the stored procedure: 
    User = THOMPSON                 MICHAEL
  3. Press F5 to stop debugging.
Parent topic: Tutorials: SQL Server COBOL Stored Procedures