1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| CREATE PROCEDURE find_bord(date_min Date, date_max Date, numero Integer(10), OUT code Integer(11), OUT num Integer(11), OUT mode VarChar(3), OUT montant Decimal(10, 3), OUT date_val Date, OUT dat_remise Date, OUT annee Integer(4), OUT log_user VarChar(15))
READS SQL DATA
begin
set @sql = concat('SELECT bo_code_pk,bo_numero,bo_mode,bo_montant,bo_dateval,bo_remise,bo_annee,u.ut_login '
' INTO code, num, mode, montant,date_val, date_remise, annee, log_user '
' FROM bord b '
' inner join utilisateur u on '
' b.bo_user_fk = u.ut_code_pk '
' WHERE 1=1');
if(date_min IS not null AND date_max IS not null) then
SET @sql = CONCAT(@sql, ' AND bo_remise between ', CHAR(39),date_min,CHAR(39), ' AND ', CHAR(39),date_max,CHAR(39));
elseif (date_min IS not null AND date_max IS null) then
SET @sql = CONCAT(@sql, ' AND bo_remise >= ', CHAR(39),date_min,CHAR(39));
elseif (date_min IS null AND date_max IS not null) then
SET @sql = CONCAT(@sql, ' AND bo_remise <=', CHAR(39),date_max,CHAR(39));
end if ;
if(numero IS not null) then
SET @sql = CONCAT(@sql, ' AND bo_numero = ', numero);
end if ;
PREPARE cmd FROM @sql;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
end
/ |
Partager