ID Number: Q66748
1.10 1.11 4.20
OS/2
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.
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.
Additional reference words: Embedded SQL for COBOL