Wrapping SELECT tests in a PROCEDURE with activity count

Database
Enthusiast

Wrapping SELECT tests in a PROCEDURE with activity count

HI;

I have a numer of tests that I run across a DB after an ETL load to validate the dataset

Each test is a negative test (ie: should return zero rows) 

EG:

Select 'COLX' , table1.colx , table1.coly , table1.colz , start_date, end_date FROM Table1 where end_date <start_date ;

Select 'COLY' , table2.colx , start_date, end_date FROM Table2 where end_date <start_date ;

Select 'COLZ' , table3.colx , table3.colz , start_date, end_date FROM Table3where end_date <start_date ;

They are diff record sets so cant be unioned. 

Is there a way to run them all in a PROCEDURE & return the activity count for all the statements?

1 REPLY
Enthusiast

Re: Wrapping SELECT tests in a PROCEDURE with activity count

you can set up a proc that uses dymanic sql, store your select statements in a queue table, call them with a select and consume and use the system variable activity_count for each statement to check for rows returned, if actvity_count > 0 the test fails...