Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2011
    Posts
    32

    Question Unanswered: for loop in informix

    Hello,

    I have to write a stored proc which will take in the comma delimited string of movie ids, and will get me a list of movie names from the dB. I have tried, but I am not sure if the syntax is correct, obviously db Vis is throwing syntax error

    Pl.Help.

    Code:
    CREATE PROCEDURE test_sp() returning VARCHAR(255);
    begin
    
    
    define set_movieIds, t_movieNames varchar(255);
    define t_movieID varchar(5);
    define t_count, t_length integer;
    
    
    let set_movieIds = '54565' ||',' || '55258' || ',' || '55711' || ',' ||'52719' || ',';
    let t_length = length(set_movieIds);
    
    for t_count IN (0 TO t_length) loop
            IF(set_movieIds[t_count] is not ',')
                    THEN
           
                     for t_count in (t_count to t_count+4)
                         let t_movieID[t_count] = set_movieIds[t_count]
                     end for;
                    
                    select movie_name into t_movieNames 
                    from tbl_cinemasource_movieinfo 
                    where movie_id = t_movieID      
                    let t_movieNames = t_movieNames || ',';
                    
            end if;
    
    
    exit when t_count = t_length;
    end loop;
    
    
    return t_movieNames;
    end;
    END PROCEDURE;
    Last edited by ab_yunus; 03-15-12 at 18:06. Reason: corrected a little

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •