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
DECLARE SQL1 VARCHAR(100);
DECLARE crsr CURSOR WITH RETURN ONLY FOR stmt;
SET SQL1 = 'SELECT * FROM DBC.TABLES;';
PREPARE stmt FROM SQL1;
Thanks Dieter !! But cant i accomplish this without using cursor in stored procedure ? Something like this :-
REPLACE PROCEDURE EDW_DB.TEST_SP1()
SELECT * FROM DBC.TABLES;