M7C6LLCp

1DROP PROCEDURE IF EXISTS USP_Unit_Test ;
2CREATE PROCEDURE `USP_Unit_Test`(IN scenario_id varchar(100))
3BEGIN
4
5DECLARE v_finished INTEGER DEFAULT 0 ;
6DECLARE v_scn varchar(1024) DEFAULT "" ;
7
8-- declare cursor for scenario queries
9 DECLARE db_cursor CURSOR FOR 
10 SELECT distinct `Query` FROM UNIT_TEST_UI.Scenarios ;
11
12 -- declare NOT FOUND handler
13 DECLARE CONTINUE HANDLER 
14 FOR NOT FOUND SET v_finished = 1 ;
15
16 OPEN db_cursor;
17
18 get_db: LOOP
19
20 FETCH db_cursor INTO v_scn ;
21 IF v_finished = 1 THEN
22 LEAVE get_db;
23 END IF;
24
25-- create temporary table for result set
26
27drop table if exists temp_table ;
28create temporary table temp_table 
29(id int(11),
30scenario_name varchar(1024),
31scenario_query varchar(1024),
32scenario_result int(11)
33) ;
34
35insert into temp_table (id,scenario_name,scenario_query)
36select id,Scenario_name,Query from UNIT_TEST_UI.Scenarios ;
37
38 -- exexute the query and update the result
39SET @u1 = CONCAT('update temp_table tt join UNIT_TEST_UI.Scenarios s on s.Scenario_name=tt.scenario_name set tt.scenario_result=(',v_scn,');') ;
40PREPARE stmt FROM @u1;
41EXECUTE stmt;
42DEALLOCATE PREPARE stmt ;
43
44END LOOP get_db;
45CLOSE db_cursor;
46
47case 
48when scenario_id='All' then
49select * from temp_table ;
50
51when (scenario_id in (select id from UNIT_TEST_UI.Scenarios)) then
52select * from temp_table where id=scenario_id ;
53
54ELSE
55SELECT 'Invalid Choice';
56
57END CASE;   
58END;