create stored procedure wich using number of rows from a table

General

create stored procedure wich using number of rows from a table

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?

3 REPLIES
Junior Supporter

Re: create stored procedure wich using number of rows from a table

Hi.

 

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; 

HTH.

Cheers.

Carlos.

Re: create stored procedure wich using number of rows from a table

Thanks @CarlosAL. It seems a good idea.

Senior Apprentice

Re: create stored procedure wich using number of rows from a table

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;