Problem calling stored procedures.
When executing a stored procedure I get a SQL error “A server cursor cannot be opened on the given statement or statements.” SQLCODE = -16937. I have a couple of stored procedures that are getting the same error. Below is my Cobol code and the create statement of the stored procedure I am using. What am I doing wrong?
MOVE "UN" TO HV-DBIO
EXEC SQL
DECLARE spcursor CURSOR for
CALL sp_SysGenIO (:HV-DBIO)
END-EXEC.
EXEC SQL OPEN spcursor END-EXEC.
EXEC SQL
FETCH spcursor
INTO
:GENERIC-SYS-UID,
:GENERIC-SEQUENCE-NUM
END-EXEC.
CREATE PROCEDURE [dbo].[sp_SysGenIO]
@DbIO Char(02)
AS
BEGIN
DECLARE @SQLfunction Char(02);
DECLARE @PriKey INT = 0;
SET @SQLfunction = @DbIO
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION tranSysGenIO
SELECT GENERIC_SYS_UID,
GENERIC_SEQUENCE_NUM
FROM SYSGEN WITH (UPDLOCK)
WHERE PRIMARY_KEY = @PriKey;
IF @SQLfunction = 'UN'
UPDATE SYSGEN
SET GENERIC_SYS_UID = GENERIC_SYS_UID 1
WHERE PRIMARY_KEY = @PriKey;
IF @SQLfunction = 'SN'
UPDATE SYSGEN
SET GENERIC_SEQUENCE_NUM = GENERIC_SEQUENCE_NUM 1
WHERE PRIMARY_KEY = @PriKey;
COMMIT TRANSACTION tranSysGenIO;
END TRY
BEGIN CATCH
DECLARE @STATE INT;
SET @STATE = ERROR_STATE();
IF XACT_STATE() = -1
BEGIN
ROLLBACK TRANSACTION tranSysGenIO;
END
ELSE IF XACT_STATE() = 1
BEGIN
COMMIT TRANSACTION tranSysGenIO;
END
END CATCH
ENDALL:
END
