Jump to content
Sign in to follow this  
shaddf

improve approach

Recommended Posts

declare l_team_id int;
DECLARE cur1 CURSOR FOR
SELECT * FROM t_summ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;
SET l_last_row_fetched=0;
OPEN cur1;
    cat_loop: LOOP
FETCH cur1 INTO l_team_id,l_teamcat_id;
/*work with the data*/
       IF l_last_row_fetched=1 THEN       /* No more rows*/
          LEAVE cat_loop;
       END IF;
  if l_team_id = inHometeamId || l_team_id = inAwayteamId THEN
    insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id);
  elseif l_team_id = inAwayteamId THEN
    insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id);
  else
    insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,'0');
  end if;
 END LOOP cat_loop;
  CLOSE cur1;
SET l_last_row_fetched=0; 

how can i improve on the above  to make it database friendli and efficient?

how can i modify it to use case statementand leave out the cursor?

Share this post


Link to post
Share on other sites

You should be able to produce the same sort of results by using INSERT... SELECT.  You may be able to reduce the whole thing down to 1 statement with some careful use of values in the SELECT.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.