output of Stored Procedure

Database
Highlighted
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 .

11 REPLIES 11
Ambassador

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;

Ambassador

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 ?

Ambassador

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 !!!

Re: output of Stored Procedure

Below query is my current query structure, and it is getting data from 1 table and in return its creating 1 result set

I want to get data from 2 table and in return 2 result set for both table

There is common XYZ column in both table, i dont want any join but the different result set for each table

 

--Current

REPLACE PROCEDURE DataBaseName.NEW_PROC_TEST

(IN IN_P_XYZ VARCHAR(3))

DYNAMIC RESULT SETS 1

 

BEGIN

DECLARE SQL1 VARCHAR(200);

DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;

 

SET SQL1 = 'SELECT * FROM DataBaseName.TableName WHERE XYZ IN (?);';

 

PREPARE S1 FROM SQL1;

 

OPEN C1 USING IN_P_XYZ;

 

END;

 

--Proposed change but its not working

 

REPLACE PROCEDURE DataBaseName.NEW_PROC_TEST

(IN IN_P_XYZ VARCHAR(3))

DYNAMIC RESULT SETS 1

 

BEGIN

DECLARE SQL1 VARCHAR(200);

DECLARE SQL2 VARCHAR(200);

 

DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;

DECLARE C2 CURSOR WITH RETURN ONLY FOR S2;

 

SET SQL1 = 'SELECT * FROM DataBaseName.123 WHERE XYZ IN (?);';

SET SQL2 = 'SELECT * FROM DataBaseName.ABC WHERE XYZ IN (?);';

 

PREPARE S1 FROM SQL1;

PREPARE S2 FROM SQL2;

 

OPEN C1 USING IN_P_XYZ;

OPEN C2 USING IN_P_XYZ;

 

END;

 

 

CALL DataBaseName.NEW_PROC_TEST('A11');

Teradata Employee

Re: output of Stored Procedure

You have to do it this way:

SET SQL1 =
 'SELECT * FROM database.table WHERE table.column = ' || :P_IN_Column || ';' ;

Re: output of Stored Procedure

Hi Thanks for help but can you please see my post again i have updated with new question.

Thanks