All Forums Database
SAP 73 posts Joined 08/14
26 Sep 2014
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 .

SAP
dnoeth 4628 posts Joined 11/04
27 Sep 2014

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
;

 

Dieter

SAP 73 posts Joined 08/14
27 Sep 2014

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;
 

SAP

dnoeth 4628 posts Joined 11/04
27 Sep 2014

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

Dieter

SAP 73 posts Joined 08/14
27 Sep 2014

Can i switch to macro in this case ?

SAP

dnoeth 4628 posts Joined 11/04
27 Sep 2014

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

Dieter

SAP 73 posts Joined 08/14
27 Sep 2014

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

SAP

You must sign in to leave a comment.