By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,563 Members | 1,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,563 IT Pros & Developers. It's quick & easy.

mysql stored procedure without concat

P: 45
HI,

i need solution for dynamic query in stored procedure without concat. How to create dynamic query like pagination passing limit value.

Expand|Select|Wrap|Line Numbers
  1. IF category='artist' THEN
  2.  
  3.     IF lim=0 THEN
  4.     SET @qry = CONCAT('SELECT distinct(fld_art_img_id) AS thumb_id, fld_art_img_file_name AS thumb_img, fld_mas_art_name AS thumb_title, fld_mas_prof_name AS profession,fld_art_img_description AS gal_des,fld_mas_art_id as rel_id,fld_gal_art_p_link as folder_name  FROM gallery_artist LEFT OUTER JOIN artist_image ON fld_gal_art_id = fld_art_img_gal_art_id AND fld_art_img_display=1 AND DATE(fld_art_img_dt_pub)<=curdate()  INNER JOIN artist_language ON fld_gal_art_artist_id = fld_art_lang_art_id
  5.                         AND fld_art_lang_lang_id =',lang_id,' INNER JOIN artist_profession ON fld_gal_art_artist_id = fld_art_prof_art_id INNER JOIN master_profession ON fld_mas_prof_id = fld_art_prof_prof_id and fld_mas_prof_name IN ("actor","actress") INNER JOIN master_artist ON fld_gal_art_artist_id = fld_mas_art_id WHERE fld_gal_art_id =',gal_id,' GROUP BY fld_art_img_id ORDER BY fld_art_img_id DESC');
  6.     ELSE
  7.     SET @qry = CONCAT('SELECT distinct(fld_art_img_id) AS thumb_id, fld_art_img_file_name AS thumb_img, fld_mas_art_name AS thumb_title, fld_mas_prof_name AS profession,fld_art_img_description AS gal_des,fld_mas_art_id as rel_id,fld_gal_art_p_link as folder_name  FROM gallery_artist LEFT OUTER JOIN artist_image ON fld_gal_art_id = fld_art_img_gal_art_id AND fld_art_img_display=1 AND DATE(fld_art_img_dt_pub)<=curdate() INNER JOIN artist_language ON fld_gal_art_artist_id = fld_art_lang_art_id
  8.                         AND fld_art_lang_lang_id =',lang_id,' INNER JOIN artist_profession ON fld_gal_art_artist_id = fld_art_prof_art_id INNER JOIN master_profession ON fld_mas_prof_id = fld_art_prof_prof_id and fld_mas_prof_name IN ("actor","actress") INNER JOIN master_artist ON fld_gal_art_artist_id = fld_mas_art_id WHERE fld_gal_art_id =',gal_id,' ORDER BY fld_art_img_id DESC LIMIT ',start,',',lim);
  9.     END IF;
  10. END IF;
  11.  
  12. PREPARE stmt FROM @qry;
  13. EXECUTE stmt;
  14. DEALLOCATE PREPARE stmt;
  15. END
  16.  
  17.  
Jul 30 '09 #1
Share this Question
Share on Google+
3 Replies


P: 60
You can take the page number and number of records per page as input parameters to the stored procedure and use them inside the query.

For example: -


Expand|Select|Wrap|Line Numbers
  1. SET @PageIndex = paramPageIndex * paramNoOfRecords;
  2. SET @NoOfRecords = paramNoOfRecords;
  3. SET @sql = CONCAT('SELECT A.Code, A.Name....FROM A WHERE <condition> LIMIT ?,?;');
  4.  
  5. PREPARE statement FROM @sql;
  6. EXECUTE statement using @PageIndex,@NoOfRecords;
  7. DEALLOCATE PREPARE statement;

Thanks,
Lauren
Aug 6 '09 #2

P: 45
hi Lauren,
Thank for reply. if it possible for without concat.
Aug 6 '09 #3

P: 60
You can try the same thing as below:

Expand|Select|Wrap|Line Numbers
  1. PREPARE statement FROM  "SELECT field1,field2,..... FROM <table> WHERE <condition> LIMIT ?,?";
  2. EXECUTE statement USING @PageIndex,@NoOfRecords;
  3. DEALLOCATE PREPARE statement; 
Hope this could help.I used to do this with CONCAT :-).

Thanks,
Lauren
Aug 6 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.