INF: Invoking Stored Procedures from Embedded SQL for COBOL

  - Microsoft Embedded SQL version 4.2 for COBOL

SUMMARY

The code below demonstrates how you can invoke a stored procedure in Microsoft SQL Server from a Microsoft COBOL program using Embedded SQL for COBOL.

Please note that there are two distinct "execute" statements available in Embedded SQL for COBOL. The execute statement used below is the Transact-SQL execute (EXEC), which is used to execute stored procedures. It is abbreviated to distinguish it from the Embedded SQL EXECUTE, which is used to execute a prepared statement. See the "Microsoft Embedded SQL for COBOL Programmer's Reference" for more information about using prepared statements.

The DECLARE SECTION is used to declare program variables that the SQL Server needs to know about. Each EXEC SQL section is a SQL batch that in this program will be compiled into a stored procedure on the SQL Server. When sqlcode = 0, this indicates that no errors or warnings were returned. The SELECT INTO :<program variable> section returns the results from the SELECT into the program variable. The colon (:) is used to distinguish program variables from SQL Server variables. Please note that if more than one row of results is returned, all but the last row will be discarded. If the result set contains more than one row of data, you should use a cursor.

MORE INFORMATION

Sample Code

    WORKING-STORAGE SECTION.

    EXEC SQL INCLUDE SQLCA END-EXEC

    EXEC SQL BEGIN DECLARE SECTION END-EXEC
    01  added-type  pic x(8).
    EXEC SQL END DECLARE SECTION END-EXEC

    PROCEDURE DIVISION.

    EXEC SQL
      exec master..sp_addtype new_type, int
    END-EXEC
    if sqlcode = 0
      EXEC SQL
        select name
        into :added-type
        from master..systypes
        where name = "new_type"
      END-EXEC
      if sqlcode = 0
        display "'"added-type"' has been added."
        EXEC SQL
          exec master..sp_droptype new_type
        END-EXEC
        if sqlcode not = 0
          perform sql-error
        else
          display "It has now been deleted."
      else
        perform sql-error
      end-if
    else
      perform sql-error
    end-if
    stop run.

    sql-error.
    display "SQL error SQLCODE=" sqlcode.