Stored procedures problem

Database

Stored procedures problem

Hi all,

I'm new to the stored procedures in teradata. I wanted to give tables and database names as parameters in stored procedures, but it is not working. is it possible to create a procedure in teradata similar to this. I tried, it throwing error.

CREATE PROCEDURE db_name.new_procedure (in_table VARCHAR(30), in_database VARCHAR(30),ot_table VARCHAR(30), ot_database VARCHAR(30))

BEGIN

CALL DBC.SysExecSQL('CREATE TABLE ' || in_database || '.' || in_table || 'as (select * from' || ot_database || '.' || ot_table || ') with data;' ) ;

END;

2 REPLIES
Enthusiast

Re: Stored procedures problem

You Should be able to create the stored proc, I just copied your code and ran in my env, the stored proc gets created without any issues. Can you please let me know what error it throws while creating?

Enthusiast

Re: Stored procedures problem

Best way to debug these dynamic sql's would be to move them to an Output variable before calling the DBC.SysExecSQL statement.

Anyways, coming to this query, looks like the issue is with the space. Please add a space in the after the "from" & try.

as (select * from '

Thank you!

Ranga