Stored procedure with select on table that is creatde in runtime

Database

Stored procedure with select on table that is creatde in runtime

How do we create a procedure that has select on a table that is created in the same stored procedure.
Ex:

CREATE PROCEDURE TEST_PROC()
BEGIN

DECLARE l_count INTEGER;

CALL SomeUsr.CREATE_TEMP_TABLE();-- create a temp table in user space
SELECT COUNT(*) INTO l_count FROM SomeUsr.TEMP_TABLE;

END

Tags (1)
4 REPLIES

Re: Stored procedure with select on table that is creatde in runtime

Error i get on compilation:

SPL1027:E(L6), Missing/Invalid SQL statement'E(3807):Object 'TEMP_TABLE' does not exist.'.
Senior Apprentice

Re: Stored procedure with select on table that is creatde in runtime

If an object doesn't exists, there should be a *warning* message, but the SP is created.

The create will only fail if there's an *error* message.

Dieter
Visitor

Re: Stored procedure with select on table that is creatde in runtime

At the time of stored procedure creation, any reference to missing database object will be reported as warning only if the creator of the stored procedure is same as the immediate owner of the SP.

If the creator (the user specified in logon stmt) is different from the immediate owner (the database in which the SP is being created), then this will be reported as error and the stored procedure will not be created.
N/A

Re: Stored procedure with select on table that is creatde in runtime

Hi

I'm facing the similar problem while creating table through stored procedure in teradata

It says Object (table) does not exists with an error msg "

Missing/Invalid SQL statement'E(3807):Object #TABLE does not exist.'.

If i creat the table and then try to execute the procedure, it gives an error "Table already created"

So in such case how we proceed ??

Thanks in advance,

Saanz :)