Run the query in procedure

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Highlighted
Enthusiast

Run the query in procedure

 

Hi All,

 

I have genarted a query through below stored procedure but is it possible to execute the query within the same procedure.

 

CREATE PROCEDURE ...

DYNAMIC RESULT SETS 1

.

.

DECLARE C1 CURSOR WITH RETURN ONLY FOR S1

.

.

SET SQLSTR = STM1 || STM2 || STM3;

PREPARE S1 FROM SQLSTR;

OPEN C1;

END;

 

Output for above stored procedure is a query like >> SEL COL1,COL2 FROM DB.TAB1 so is there a way to change this procedure to generate as well as execute the query in it.

 

Thanks.


Accepted Solutions
Teradata Employee

Re: Run the query in procedure

Just loop and concatenate? For example

 

OPEN C1;
FETCH C1 INTO NextLine;
REPEAT
  SET Stmt = Stmt || ' ' || NextLine;
  FETCH C1 INTO NextLine;
UNTIL (SQLCODE <>  0)
END REPEAT; CLOSE C1;

 

 

 

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Run the query in procedure

Hi tera_user,

 

Just go for EXECUTE IMMEDIATE.

Teradata Employee

Re: Run the query in procedure

FETCH from the first cursor and PREPARE/OPEN a second one?

Enthusiast

Re: Run the query in procedure

 

Hello,

 

when i FETCH  for the 2nd time into a variable then it overrides the value so could you please suggest how to use the 2nd cursor to get the complete query into another variable which i can execute.

 

Thanks.

Teradata Employee

Re: Run the query in procedure

Just loop and concatenate? For example

 

OPEN C1;
FETCH C1 INTO NextLine;
REPEAT
  SET Stmt = Stmt || ' ' || NextLine;
  FETCH C1 INTO NextLine;
UNTIL (SQLCODE <>  0)
END REPEAT; CLOSE C1;

 

 

 

Enthusiast

Re: Run the query in procedure

 

Thanks Fred & Waldar. Now i am able to get entire query in a string.