1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| CREATE PROCEDURE SP_CHECK_TABLES (
date_bilan date)
returns (
nom_table varchar(31),
nom_colonne varchar(31),
nb_lignes varchar(10))
as
declare variable sqlstmt varchar(128);
BEGIN
FOR SELECT f.RDB$RELATION_NAME, f.RDB$FIELD_NAME
FROM RDB$RELATION_FIELDS f
INNER JOIN RDB$FIELDS t
ON f.RDB$FIELD_SOURCE = t.RDB$FIELD_NAME
AND t.RDB$FIELD_TYPE = 12 -- 12 => DATE, 35 => TIMESTAMP
INNER JOIN RDB$RELATIONS r
ON f.RDB$RELATION_NAME = r.RDB$RELATION_NAME
AND r.RDB$VIEW_BLR IS NULL
AND (r.RDB$SYSTEM_FLAG IS NULL OR r.RDB$SYSTEM_FLAG = 0)
/* pour ne filtrer que sur les champs date dont le nom dans la base est */
/* date_bilan ou date_mois */
WHERE
(
( f.RDB$FIELD_NAME = 'Date_Bilan') or (f.RDB$FIELD_NAME = 'Date_Mois')
/* Si on est très orthodoxe, on aurait
(Upper(Trim(f.RDB$FIELD_NAME)) = 'DATE_BILAN') or (Upper(Trim(f.RDB$FIELD_NAME)) = 'DATE_MOIS')
*/
)
INTO
:Nom_Table,
:Nom_Colonne
DO
BEGIN
SqlStmt = 'SELECT DISTINCT COUNT(*) FROM ' || TRIM(Nom_Table) ||
' WHERE (' || TRIM(Nom_Colonne) || ' = ''' || :Date_Bilan || ''')' ||
' HAVING(count(*) > 0) ';
EXECUTE STATEMENT SqlStmt INTO :nb_lignes;
SUSPEND;
END
END |