Create Volatile Table by calling a stored procedure

Database

Create Volatile Table by calling a stored procedure

I was trying to to check if it is possible to create a volatile table in Teradata by taking the out put of a stored procedure. Here is the sample code and the error I am saying. Please let me know if it is possible in Teradata or am I doing something wrong here.

 

Stored Procedure:
REPLACE PROCEDURE schema1.stored_proc1 (N INTEGER)
DYNAMIC RESULT SETS 1 SQL SECURITY INVOKER
BEGIN
DECLARE queryInput VARCHAR(1000);
DECLARE resultSet CURSOR WITH RETURN ONLY FOR sqlStatement;
SET queryInput = 'SELECT top ' || N || ' * from schema1.table1';
PREPARE sqlStatement FROM queryInput;
OPEN resultSet;
END;

 

The stored procedure works fine when I tried to call it independently but when I try calling it while creating a volatile table it throws an error shown below. I wanted to check if it is possible to do it Teradata or do I need to do it differently. 

 

CREATE MULTISET VOLATILE TABLE vt_tmp1
AS
(
CALL schema1.stored_proc1 (10)
)
WITH DATA NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS;

Throws an error - "Create Table Failed. 3707: Syntax error, expected something like a 'CHECK' keyword between '(' and the 'CALL' keyword."

1 REPLY
Junior Contributor

Re: Create Volatile Table by calling a stored procedure

You can't do that. If you need a VT why don't you create it within the SP?