-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `commentary`(IN fieldname text,IN orderby text, IN param2 text)
BEGIN
SET @res:=0;
SET @id:=0;
SET @s=concat('select rownumber from(
						select result,
						@res := @res + 1 as rownumber
							from(
                                    SELECT `t`.`commentary` AS `t0_c1`, `t`.`owner` AS `t0_c4`, `t`.`datespr` AS `t0_c3`,
                                    aOwner.nick_name as nick_name, `t`.`id` AS `t0_c0`,
                                    `aOwner`.`id` AS `t1_c0`, `aOwner`.`change_date` AS `t1_c1`,
                                     `aOwner`.`first_name` AS `t1_c2`,
                                    `aOwner`.`middle_name` AS `t1_c3`,
                                     `aOwner`.`last_name` AS `t1_c4`,
                                    `aOwner`.`nick_name` AS `t1_c5`,
                                     `aOwner`.`email` AS `t1_c6`,
                                     `aOwner`.`gender` AS `t1_c7`,
                                    `aOwner`.`birth_date` AS `t1_c8`,
                                    `aOwner`.`site` AS `t1_c9`,
                                    `aOwner`.`about` AS `t1_c10`,
                                     `aOwner`.`location` AS `t1_c11`,
                                    `aOwner`.`status` AS `t1_c12`,
                                    `aOwner`.`access_level` AS `t1_c13`,
                                     `aOwner`.`last_visit` AS `t1_c14`,
                                    `aOwner`.`registration_date` AS `t1_c15`,
                                    `aOwner`.`avatar` AS `t1_c16`,
                                     `aOwner`.`hash` AS `t1_c17`,
                                    `aOwner`.`email_confirm` AS `t1_c18` ,
                                    @id := @id + 1 AS rownumber ,
                                    CASE WHEN ',fieldname,' like \'',param2,'\'',' THEN @id ELSE 0 END as result
                                    FROM `spr_tncommentary` `t`
                                    LEFT OUTER JOIN `yupe_user_user` `aOwner` ON (`t`.`owner`=`aOwner`.`id`) ',

                                    orderby,'
                                    ) as rr
                                    ) as rrr where result>0');
PREPARE mselect FROM @s;
EXECUTE mselect;
deallocate PREPARE mselect;
END

CALL commentary('commentary','(БРОНХИАЛЬНАЯ АСТМА)%','where 1=1','order by datespr desc,owner asc','');
