Need to design a Stored Procedure which calls multiple test SQL's and post the answer into another T

Database
Enthusiast

Need to design a Stored Procedure which calls multiple test SQL's and post the answer into another T

Hello Everyone,

 

I need to design a stored procedure which should do the following steps:

1. Execute multiple TEST Sql's and if Activity count > 0 then Pass else Fail

2. I want the above result Pass or Fail to be posted as data into another table.

 

Basically my objective is to automate testing in my project. I want to run all the quieries at once and then post the result for the client to see in one table .

Can anyone please help me on this? Does anyone have some already written Stored Procedure to do this?

 

Thanks

Lovey

3 REPLIES
rjg
Supporter

Re: Need to design a Stored Procedure which calls multiple test SQL's and post the answer into anoth

Lovley,

This should work, I don't see any benefit of it being in a SP, just a BTEQ would do.

 

SyntaxEditor Code Snippet

CREATE MULTISET TABLE db.tb  
      (
      qry_id Smallint,
      Status CHAR(4))PRIMARY INDEX ( qry_id );
 
 
 REPLACE      PROCEDURE db.proc_nm(    
 )Begin


 insert into db.tb values(1,);
 insert into db.tb values(2,);............................

insert into db.tb
SELECT    1, 'Pass' where exists (Query that returns rows);

insert into db.tb
SELECT    2, 'Pass' where exists (Query that returns no rows); ............................. END;


sel qry_id,coalesce(status,'Fail') from db.tb
Qualify row_number() over (partition by qry_id order by status desc) = 1 ;
Qry_id Status
1            Pass
2            Fail


Rglass
Enthusiast

Re: Need to design a Stored Procedure which calls multiple test SQL's and post the answer into anoth

Hello rjg,

 

Thankyou for your reply.

 

But referring to the insert statements in your reply, i cannot pre determine which query will return 0 or results greater than 0.

 

If there are 100 tables to be tested, it will be an effort to design the insert statements 100 times.

 

Regards,
Lovey

 

Regards,

Lovey

rjg
Supporter

Re: Need to design a Stored Procedure which calls multiple test SQL's and post the answer into anoth

Lovely,

 

The first insert just enters a qry_id for each query you are going to test

then

if the test query returns results 'Pass' will be inserted into the table.

If it returns 0 records nothing will be inserted.

 

The query run after will retun 'Pass' where it was inserted and Fail where nothing was inserted.

 

Rglass