Stored Procedures

Stored Procedures as External Function

Declare a Local External Function in n_tr_lm object.  (In this  example, SQLCA is  declared to be of type n_tr_lm, a descendant of n_tr)

FUNCTION long get_next_id(string TABLE, string COLUMN, long INCREMENT) &
   RPCFUNC ALIAS FOR “QUAD0076.dbo.sp_qg_get_next_id”

Powerbuilder script to call stored proc:

ll_next_job = SQLCA.get_next_id(“JOB_QUEUE”, “JOB_ID”, 1)

Calling a stored procedure using a local function which is not an external function

Example:

DECLARE redo_list PROCEDURE FOR
    @li_rc = QUAD0035.dbo.ml_create_cm_redo_list
    @CmEvent = :al_cm_id,
    @Output_List_id = :ll_output_id OUT
USING SQLCA;

EXECUTE redo_list;

IF SQLCA.SQLCode = -1 THEN
   ls_msg = SQLCA.SQLErrText
   MessageBox("of_redo_stored_Proc", ls_msg)
ELSE
   // Put the return value into the var and close the declaration.
   FETCH redo_list INTO :li_rc, :ll_output_id;
   CLOSE redo_list;
END IF

Calling a stored procedure with an output argument

In our Sybase environment, I found that the “dbo” was required in the stored procedure name

DECLARE redo_list PROCEDURE FOR
   @li_rc = QUAD0035.dbo.ml_create_cm_redo_list
   @CmEvent = :al_cm_id,
   @Output_List_id = :ll_output_id OUT
USING SQLCA;

EXECUTE redo_list;

IF SQLCA.SQLCode = -1 THEN
   ls_msg = SQLCA.SQLErrText
   MessageBox("of_redo_stored_Proc", ls_msg)
ELSE
   // Put the return value into the var and close the declaration.
   FETCH redo_list INTO :li_rc, :ll_output_id;
   CLOSE redo_list;
END IF