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?
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
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.
The first insert just enters a qry_id for each query you are going to test
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.