mysql stored procedure without concat

shanmugamit

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

 
IF category='artist' THEN
 
    IF lim=0 THEN

    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

                        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');

    ELSE

    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

                        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);

    END IF;

END IF;
 
PREPARE stmt FROM @qry;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END

SLauren

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

 SET @PageIndex = paramPageIndex * paramNoOfRecords;

SET @NoOfRecords = paramNoOfRecords;

SET @sql = CONCAT('SELECT A.Code, A.Name....FROM A WHERE <condition> LIMIT ?,?;');
 
PREPARE statement FROM @sql;

EXECUTE statement using @PageIndex,@NoOfRecords;

DEALLOCATE PREPARE statement;

Thanks,
Lauren

shanmugamit

hi Lauren,
Thank for reply. if it possible for without concat.

SLauren

You can try the same thing as below:

Expand|Select|Wrap|Line Numbers

 PREPARE statement FROM  "SELECT field1,field2,..... FROM <table> WHERE <condition> LIMIT ?,?";

EXECUTE statement USING @PageIndex,@NoOfRecords;

DEALLOCATE PREPARE statement;

Hope this could help.I used to do this with CONCAT :-).

Thanks,
Lauren

mysql stored procedure without concat

re: mysql stored procedure without concat

re: mysql stored procedure without concat

re: mysql stored procedure without concat

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about