Teradata stored procedure with multiple dynamic sql statements.

Database

Teradata stored procedure with multiple dynamic sql statements.

Hi,

I have a Teradata stored procedure which accepts a dynamic sql query as a parameter and executes the query inside the procedure body.

What is the best way to make the SP into a format which executes multiple queries in a single parameter delimited?

11 REPLIES
Teradata Employee

Re: Teradata stored procedure with multiple dynamic sql statements.

In a SQL Stored Procedure, dynamic SQL must be a single statement.

 

If the input string contains multiple statements, your procedure logic could extract them one at a time and execute them in sequence.

Junior Contributor

Re: Teradata stored procedure with multiple dynamic sql statements.

In fact you can have multiple DMLs in Dynamic SQL, you just have to wrap it in a BEGIN/END REQUEST block :-)

DECLARE sql_stmt VARCHAR(5000)  CHARACTER SET Unicode;   
SET sql_stmt = 'INSERT INTO ...;DELETE ...;UPDATE ...;';
BEGIN REQUEST
   EXECUTE IMMEDIATE sql_stmt;
END REQUEST;

 

Re: Teradata stored procedure with multiple dynamic sql statements.

I am trying to pass the SQL statement as a single parameter into the procedure. Can I use the same format like the multiple insert commands separated by semicolon for the parameter?
Junior Contributor

Re: Teradata stored procedure with multiple dynamic sql statements.

Yes, simply try it.

Re: Teradata stored procedure with multiple dynamic sql statements.

It throws an error like Failed to execute query: [Teradata Database] [TeraJDBC 15.10.00.35] [Error 5568] [SQLState HY000] XYZ:SQL statement is not supported within a stored procedure,.when I try to pass multiple insert statements seperated by semicolons

Junior Contributor

Re: Teradata stored procedure with multiple dynamic sql statements.

Did you add the BEGIN REQUEST ... END REQUEST; block?

Re: Teradata stored procedure with multiple dynamic sql statements.

This is my SP code.

 

SyntaxEditor Code Snippet

replace xyz(IN QUERY  CHAR(10000))DYNAMIC RESULT SETS 1
BEGIN

DECLARE SQL1 VARCHAR(100);
DECLARE crsr CURSOR WITH RETURN ONLY FOR stmt;

EXECUTE IMMEDIATE QUERY;
SET SQL1 = 'SELECT 1 FROM table1;';
PREPARE stmt FROM SQL1;
OPEN crsr;
END;

 I am trying to give multiple insert statements as an argument. 

Teradata Employee

Re: Teradata stored procedure with multiple dynamic sql statements.

I had never tried that! Always learning something new.

Junior Contributor

Re: Teradata stored procedure with multiple dynamic sql statements.



replace xyz(IN QUERY  CHAR(10000))DYNAMIC RESULT SETS 1
BEGIN
   DECLARE SQL1 VARCHAR(100);
   DECLARE crsr CURSOR WITH RETURN ONLY FOR stmt;
   BEGIN REQUEST
      EXECUTE IMMEDIATE QUERY;
   END REQUEST;
   SET SQL1 = 'SELECT 1 FROM table1;'; 
   PREPARE stmt FROM SQL1; 
   OPEN crsr; 
END;