Under teradata, I have created a stored procedure in which I use the number of rows from a table in a variable. This variable will serve me in my treatment. The problem is the schema of the table wich I want to calculate its row number, is an input parameter.
This is my stored procedure :
EPLACE PROCEDURE DATABASE.PROC1 (IN VAR INTEGER, IN SCHEMA VARCHAR(20)) BEGIN DECLARE nbr_lignes INTEGER; DECLARE compteur INTEGER; SET compteur=0; SELECT COUNT(*) FROM DATABASE .TABLE1 INTO nbr_lignes; WHILE (compteur<=nbr_lignes) DO BEGIN CALL DBC.SYSEXECSQL('DELETE FROM DATABASE.TABLE2'); ... ... ... SET compteur=compteur+pas; END; END WHILE; END;
I want to change this expression,SELECT COUNT(*) FROM DATABASE.TABLE1 INTO nbr_lignes;, to have a parameterized database of the TABLE1:
I try to use this expression, but it doesn't work:
SET nbr_total_lignes= CALL DBC.SYSEXECSQL('SELECT COUNT(*) FROM '||SCHEMA||'.TABLE1');
Do you have any idea to retrieve the number of rows in a parameter of the stored procedure?
You can create a VOLATILE TABLE using the desired table name as INPUT parameter in the SP:
CALL DBC.SYSEXECSQL('CREATE VOLATILE MY_TABLE AS '|| p_TABLE_NAME || ' WITH DATA ON COMMIT PRESERVE ROWS');
Then you can do the COUNT from it:
SELECT COUNT(*) FROM MY_TABLE INTO nbr_lignes;
Using Dynamic SQL with INTO is a bit complicated, afaik you need a cursor like this:
BEGIN DECLARE nbr_total_lignes INTEGER; DECLARE sqlstr VARCHAR(1000); DECLARE cnt_cursor CURSOR FOR S; SET sqlstr = 'SELECT COUNT(*) FROM ' || SCHEMA || '.TABLE1'; PREPARE S FROM sqlstr; OPEN cnt_cursor; FETCH cnt_cursor INTO nbr_total_lignes; CLOSE cnt_cursor; END;