CODEWITHSUNDEEP
sqlrstored-proceduresteradata
MidnightDataGeek
MidnightDataGeek

Reputation: 938

Teradata stored procedure with dynamic parameters called from R script

I need to extract some data from Teradata to process in R. I have around 84 Dep/sec keys with most of them having a different time span so my thought was to create a stored procedure in Teradata that will accept the Dep, Sec and Dates as parameters. I could then loop over the list in R calling the SP each time to create my data set.

The SP I have created to test this idea is a very simple one but I can't get it to work.

CREATE PROCEDURE procTest4 (IntN integer)
BEGIN
CALL DBC.SysExecSQL('SELECT top' || IntN || '*
from TableName');
END;

Teradata does create the SP but I don't know how to execute it and pass the paramters to it. When I try: Call procText4(10) I get the following error:

5568: SQL statement is not supported within a stored procedure.

The only other option for me is to create the SQL string in R and then run it from there but there is multiple passes of SQL which create volatile tables and the RODBC package doesn't seem to like them, plus it's a very messy way of doing it.

Any help is much appreciated.

Upvotes: 1

Views: 4105

Answers (1)

dnoeth
dnoeth

Reputation: 60462

The syntax for returning a result set from a Stored Procedure using Dynamic SQL is a bit complex:

CREATE PROCEDURE procTest4 (IntN INTEGER)
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE SqlStr VARCHAR(1000);
   DECLARE rslt CURSOR WITH RETURN ONLY FOR stmt;
   SET SQLStr = 'SELECT top ' || IntN || ' * from TableName';
   PREPARE stmt FROM SqlStr;
   OPEN rslt;
END;

But you should double check if you can rewrite those loops...

Upvotes: 5

Related Questions