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
– Haytem BrB, Commented Apr 25, 2016 at 16:25
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
– MidnightDataGeek, Commented Apr 25, 2016 at 16:39
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
– Haytem BrB, Commented Apr 25, 2016 at 16:44
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
– MidnightDataGeek, Commented Apr 25, 2016 at 18:28

dnoeth · Accepted Answer · 2016-04-25 16:42:04Z

5

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

answered Apr 25, 2016 at 16:42

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MidnightDataGeek Over a year ago

Awesome Thank you! I spent the whole day looking online for a solution and asking around the office but nobody seems to use SP's? My actual SQL query is around 200 lines long and makes 4 passes of SQL, am I being naive in thinking I can simply substitute the real query for the one above? Also is this documented anywhere? I'd like to get a better understanding of this because I spend a lot of time extracting data to use in R and I think this will take a lot of that pain away.

dnoeth Over a year ago

@MidnightDataGeek: Have a look at the SQL Stored Procedures and Embedded SQL, there are examples: info.teradata.com/HTMLPubs/DB_TTU_14_00/SQL_Reference/… But always double check if the sysntax is not Embedded SQL only

Collectives™ on Stack Overflow

Teradata stored procedure with dynamic parameters called from R script

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related