Hi,
The book "SAS/ACCESS® 9.1.3 Supplement for DB2 under z/OS (SAS/ACCESS for Relational Databases" is mentioned as below to call a stored procedure which has out parameters
proc sql;
connect to db2;
%let INOUT=2;
create table sasresults as select * from connection to db2
(call STORED_PROC (1,:INOUT,:OUT));
quit;
By referring the above sample I tried to call the stored procedure SASSP0004 but it throws an error as shown below
148 proc sql;
149 connect to db2(user=Iteration1 password=XXXXXXXXXX
150 database=pbsdev);
151 create table sasresults as select * from connection to db2
152 (call SASSP0004(:SYS_OUT,0,2852700031)) ;
ERROR: CLI prepare error: [IBM][CLI Driver][DB2/NT] SQL0312N The host variable "SYS_OUT" is used in a dynamic SQL statement, a
view definition, or a trigger definition. SQLSTATE=42618
SQL statement: call SASSP0004(:SYS_OUT,0,2852700031).
Procedure Body:
CREATE PROCEDURE SASSP0004 (
OUT SYS_OUT varchar(5),
IN DELIVERY_ID_ST DECIMAL(10,0),
IN DELIVERY_ID_EN DECIMAL(10,0) )
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR with return FOR SELECT * FROM db2table where delv_id between DELIVERY_ID_ST and DELIVERY_ID_EN;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SELECT 'SYS' into SYS_OUT FROM SYSIBM.SYSDUMMY1;
OPEN C1;
END;
Kindly revert with the fix that should be done to the above PROC SQL code.