Error in simplest stored procedure

Database
Enthusiast

Error in simplest stored procedure

 

I am trying to populate a table using a stored procedure.  It is very simple, yet I am getting an error saying "(-3706)Syntax error: Invalid  SQL Statement".

 

Any ideas why?  Here is what I have now:

 

CREATE MULTISET TABLE testdb.table_1  (x bigint);

 

CREATE PROCEDURE testdb.my_sp  () MODIFIES SQL DATA
 BEGIN
 DECLARE i  BIGINT;
 SET i = 0;
 WHILE (i < 10) DO
         INSERT INTO testdb.table_1  (x) VALUES (i) ;
         set i = i + 1;
 END WHILE;
 END;


Accepted Solutions
Junior Contributor

Re: Error in simplest stored procedure

I just tried it, it returns a generic 3706 when I run both Create Table and Create Procedure as a Multi Statement Request, i.e. both are submitted as a single request, e.g. F9 in SQL Assistant. Try using two separate requests, i.e. F5.

1 ACCEPTED SOLUTION
5 REPLIES
Enthusiast

Re: Error in simplest stored procedure

A clarification:

The create table statement is separate; I included it just to show how the table was created.  I can insert into the table manually just fine.  Only when I use the stored procedure do I get the error message.

 

Junior Contributor

Re: Error in simplest stored procedure

Your syntax is fine.

Do you get that error when you try to Create the SP or when you Call it?

What's your Teradata release and which client do you use?

Enthusiast

Re: Error in simplest stored procedure

Hello,

 

I get the error when trying to create the SP.  The Teradata release says 16.10 (the vmware version).  I am using an ODBC client.

 

Junior Contributor

Re: Error in simplest stored procedure

I just tried it, it returns a generic 3706 when I run both Create Table and Create Procedure as a Multi Statement Request, i.e. both are submitted as a single request, e.g. F9 in SQL Assistant. Try using two separate requests, i.e. F5.

Enthusiast

Re: Error in simplest stored procedure

I was doing what you suggested.  So, I tried it again but with the SQL assistant this time as you were doing.  And then I got a more useful error message: no create procedure privilege :-)

 

Thank you very  much for your help.  It all seems to be ok now.