Teradata stored procedure with dynamic parameters called from R script

Question

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.

You can't do a SELECT with sysexecsql. You need to OPEN/FETCH it. Why do you need a SP for that anyway? — Haytem BrB
I couldn't think of any other way top pass my parameters accross? I need the query to be dynamic. If you have any suggests I'd be more than happy to try them. Cheers — MidnightDataGeek
Instead of Select you can use Insert, first you insert your data into a temp table and query it afterwards, I didn't understand exactly what are you trying to achieve here so I'm just speculating. — Haytem BrB
I have limited spool space at work so I am trying to get around this by incrementally building up a table in R of the data I need. I want my qry to be dynamic in that I might change the Dept's or time periods etc. I did this before in Access but the syntax was much easier to get your head around. — MidnightDataGeek

dnoeth dnoeth · Accepted Answer · 2016-04-25T16:42:04

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

Teradata stored procedure with dynamic parameters called from R script

1 Answers