An Insider's Guide to using Teradata Stored Procedures with SAS/ACCESS

[#1]

There aren't many examples of calling Teradata stored procedures from SAS. So, I decided to create one. This article will walk through creating two very simple Teradata stored procedures and calling them from SAS. The code is very simple and easy to modify.

Steps:

1. Create a test table in Teradata
2. Create two Teradata stored procedures
3. Call the stored procedures from SAS using explicit pass-through
4. Verify the results

Create a test table in Teradata

libname mytera teradata server=vat user=myuser password=mypassword;

proc sql;
   connect using mytera;
   execute (create table addtest(myCol varchar(20))) by mytera;
   execute (commit) by mytera;
quit;

Create two Teradata stored procedures

These two examples aren't going to win awards for complexity but they are easy to understand. I used Teradata SQL Assistant to create these procedures. I could have used BTEQ (.compile file=myfilename.sql) to create the procedures. On the other hand...

We cannot use SAS explicit pass-thru to run the code to create the  stored procedures. 

Important: SAS cannot create Teradata stored procedures.

AddRowsSP() - Issues INSERT statements to add 4 rows the the addtest table.

AddDataSP() - Takes a string as input and adds it to the addtest table.

The following code creates my Teradata stored procedures. I created these using the account I am using in my LIBNAME statements.

-- Stored Procedure 1

CREATE PROCEDURE AddRowsSP()
  BEGIN
     INSERT INTO addtest (myCol) VALUES ('This');
     INSERT INTO addtest (myCol) VALUES ('Works');
     INSERT INTO addtest (myCol) VALUES ('Great');
     INSERT INTO addtest (myCol) VALUES ('I hope!');
  END;

-- Stored Procedure 2

CREATE PROCEDURE AddDataSP (
   IN in_myData VARCHAR(20)
)
BEGIN
   INSERT INTO addtest (myCol) VALUES (:in_myData);
END;

Call the stored procedures from SAS using explicit pass-through

Here is the SAS code that calls the stored procedures. Notice that I added MODE=TERADATA to my LIBNAME statement!

libname tdmode teradata server=vat user=myuser password=mypassword mode=teradata;

proc sql;
   connect using tdmode;
   execute (call myuser.AddRowsSP()) by tdmode;
   execute (call AddDataSP('Added by AddDataSP')) by tdmode;
quit;

These examples add data to a table. This works fine.

Important: SAS cannot read data via a Teradata stored procedure.

Verify the results

proc sql;
   select * from mytera.addtest;
quit;

Here is the output

Yeah! It worked!

Here is the complete code!

libname mytera teradata server=mytera user=myuser password=mypassword;

proc sql;
   connect using mytera;
   execute (create table addtest(myCol varchar(20))) by mytera;
   execute (commit) by mytera;
quit;


libname tdmode teradata server=mytera user=myuser password=mypassword mode=teradata;

proc sql;
   connect using tdmode;
   execute (call myuser.AddRowsSP()) by tdmode;
   execute (call AddDataSP('Added by AddDataSP')) by tdmode;
quit;

proc sql;
   select * from mytera.addtest;
quit;

   

I know what you are thinking. What do the failures look like? That is a great question. It would be wrong to not include examples of failure!

Here is what happens if you try to create a Teradata stored procedure using SAS.

74   libname tfail teradata server=mytera user=myuser password=XXXXXX mode=teradata;

NOTE: Libref TFAIL was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: vat

75   proc sql;
76      connect using tfail;
77      execute (
78                CREATE PROCEDURE AddRowsSP()
79                BEGIN
80                   INSERT INTO addtest (myCol) VALUES ('This');
81                   INSERT INTO addtest (myCol) VALUES ('Works');
82                   INSERT INTO addtest (myCol) VALUES ('Great');
83                   INSERT INTO addtest (myCol) VALUES ('I hope!');
84                END;
85
86              ) by tfail;
ERROR: Teradata execute: Syntax error: Invalid  SQL Statement.
87   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.12 seconds
      cpu time            0.03 seconds

What happens when you try to read data from a Teradata stored procedure? Let's see...

Here is the very spiffy stored procedure! Trust me, this works... I actually tested it. 

CREATE PROCEDURE ReadDataSP ()
   DYNAMIC RESULT SETS 1
BEGIN
   DECLARE cur1 CURSOR WITH RETURN ONLY FOR 
      SELECT * FROM addtest;
   OPEN cur1;
END;

Here is the SAS code!

libname tdmode teradata server=mytera user=myuser password=mypassword mode=teradata;
proc sql;
  connect using tdmode;
  execute (call ReadDataSP()) by tdmode; 
quit; 

Here is the failure! Kaboom!

88   proc sql;
89      connect using tdmode;
90      execute (call ReadDataSP()) by tdmode;
ERROR: Teradata execute: READDATASP:The recipient of the result set does not support dynamic result
       sets.
91   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.13 seconds
      cpu time            0.04 seconds

That's it! That's all I know...

Thanks to Greg O and Austin S for more insights than I can count!

[kiranv_]

Easy to understand and precise article

[ashna]

Thanks for the article.

I have Teradata store procedures already created and want to execute them through SAS. 

In your article you have mentioned it's failure. Is any other way to do this ?

[JBailey]

Hi @ashna,

You can execute a stored procedure using SAS but you can't create a a stored procedure using SAS. To create the stored procedure you could use a Teradata utility like BTEQ.

Best wishes,

Jeff