output of Stored Procedure

Database
SAP
Enthusiast

output of Stored Procedure


REPLACE PROCEDURE EDW_DB.TEST_SP1()
BEGIN

DECLARE SQL1 VARCHAR(100);
SET SQL1 = 'SELECT * FROM DBC.TABLES;';
CALL DBC.SYSEXECSQL(SQL1);

END
;

Comiliation is successfull . but i am unable to get an output while calling the procedure. My requirement is to select the records while running the procedure .

6 REPLIES
Junior Contributor

Re: output of Stored Procedure

Providing an error message instead of "unable to get an output" would be helpful.

SysExecSQL/EXECUTE don't support SELECTs.

You shoud read the Stored procedures manual, there's a chapter on how to use cursors in Dynamic SQL.

REPLACE PROCEDURE TEST_SP1()
DYNAMIC RESULT SETS 1
BEGIN

DECLARE SQL1 VARCHAR(100);
DECLARE crsr CURSOR WITH RETURN ONLY FOR stmt;

SET SQL1 = 'SELECT * FROM DBC.TABLES;';

PREPARE stmt FROM SQL1;
OPEN crsr;

END
;
SAP
Enthusiast

Re: output of Stored Procedure

Thanks Dieter !! But cant i accomplish this without using cursor in stored procedure ? Something like this :-

REPLACE PROCEDURE EDW_DB.TEST_SP1()

BEGIN

SELECT * FROM DBC.TABLES;

END;

Junior Contributor

Re: output of Stored Procedure

No, this is how returning answer sets from a SP is implemented (based on Standard SQL)

SAP
Enthusiast

Re: output of Stored Procedure

Can i switch to macro in this case ?

Junior Contributor

Re: output of Stored Procedure

Of course can you use select in a macro, but no dynamic SQL.

SAP
Enthusiast

Re: output of Stored Procedure

Thanks Dieter !!!! I don have dynamic SQL . So i wil go for macro !!!