1

I'm newbie to MySQL and trying to write a Stored Procedure with IF statement and UNION ALL in Stored Procedure. But I'm not sure where to add Where Condition in the Query.

Here I'm trying:

BEGIN
SELECT 'ele_certificate' AS CERTIFICATE, `ele_certificate` AS STATUS, `property_id` AS pid, `ELEcertificate_issue_date` AS OldExpiry, `ELEcertificate_expiry_date` AS NewExpiry, `ELEcertificate_duration` AS Duration, `ELEcertNotes` AS Notes, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'eml_certificate', `eml_certificate`, `property_id`, `EMLcertificate_issue_date`, `EMLcertificate_expiry_date`, `EMLcertificate_duration`, `EMLcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'epc_certificate', `epc_certificate`, `property_id`, `EPCcertificate_issue_date`, `EPCcertificate_expiry_date`, `EPCcertificate_duration`, `EPCcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'fir_certificate', `fir_certificate`, `property_id`, `FIRcertificate_issue_date`, `FIRcertificate_expiry_date`, `FIRcertificate_duration`, `FIRcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'gas_certificate', `gas_certificate`, `property_id`, `GAScertificate_issue_date`, `GAScertificate_expiry_date`, `GAScertificate_duration`, `GAScertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'hmo_certificate', `hmo_certificate`, `property_id`, `HMOcertificate_issue_date`, `HMOcertificate_expiry_date`, `HMOcertificate_duration`, `HMOcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'ins_certificate', `ins_certificate`, `property_id`, `INScertificate_issue_date`, `INScertificate_expiry_date`, `INScertificate_duration`, `INScertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id;
END

This procedure gives me proper result, I've applied UNION ALL on single table to convert columns into rows.

When I'm trying to pass parameters to procedure, I'm writing following Query:

BEGIN
SET @Certificates = ("SELECT 'ele_certificate' AS CERTIFICATE, `ele_certificate` AS STATUS, `property_id` AS pid, `ELEcertificate_issue_date` AS OldExpiry, `ELEcertificate_expiry_date` AS NewExpiry, `ELEcertificate_duration` AS Duration, `ELEcertNotes` AS Notes, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'eml_certificate', `eml_certificate`, `property_id`, `EMLcertificate_issue_date`, `EMLcertificate_expiry_date`, `EMLcertificate_duration`, `EMLcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'epc_certificate', `epc_certificate`, `property_id`, `EPCcertificate_issue_date`, `EPCcertificate_expiry_date`, `EPCcertificate_duration`, `EPCcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'fir_certificate', `fir_certificate`, `property_id`, `FIRcertificate_issue_date`, `FIRcertificate_expiry_date`, `FIRcertificate_duration`, `FIRcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'gas_certificate', `gas_certificate`, `property_id`, `GAScertificate_issue_date`, `GAScertificate_expiry_date`, `GAScertificate_duration`, `GAScertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'hmo_certificate', `hmo_certificate`, `property_id`, `HMOcertificate_issue_date`, `HMOcertificate_expiry_date`, `HMOcertificate_duration`, `HMOcertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id
UNION ALL
SELECT 'ins_certificate', `ins_certificate`, `property_id`, `INScertificate_issue_date`, `INScertificate_expiry_date`, `INScertificate_duration`, `INScertNotes`, `property_code`, `property_added_date`, `property_address_1`, `property_address_2`, `property_address_3`, `property_city`, `property_cluster`, `property_area`, `property_postcode`, `property_landlord_id`, `property_status`, CONCAT(tbl_landlord.landlord_first_name,' ',tbl_landlord.landlord_middle_name,' ',tbl_landlord.landlord_last_name) AS fullName, tbl_landlord.landlord_work_number, tbl_landlord.landlord_mobile_number, tbl_landlord.landlord_main_email
FROM tbl_property LEFT OUTER JOIN tbl_landlord ON tbl_property.property_landlord_id=tbl_landlord.landlord_id");
IF datepickerfilter1 != 0 AND datepickerfilter2 != 0 THEN
SELECT @Certificates + WHERE 
ELEcertificate_expiry_date BEGIN datepickerfilter1 AND datepickerfilter2 OR 
EMLcertificate_expiry_date BEGIN datepickerfilter1 AND datepickerfilter2 OR 
EPCcertificate_expiry_date BEGIN datepickerfilter1 AND datepickerfilter2 OR 
FIRcertificate_expiry_date BEGIN datepickerfilter1 AND datepickerfilter2 OR 
GAScertificate_expiry_date BEGIN datepickerfilter1 AND datepickerfilter2 OR 
HMOcertificate_expiry_date BEGIN datepickerfilter1 AND datepickerfilter2 OR 
INScertificate_expiry_date BEGIN datepickerfilter1 AND datepickerfilter2;
PREPARE smtp FROM @Certificates;
EXECUTE smtp;
END IF;
IF datepickerfilter1 != 0 AND datepickerfilter2 = 0 THEN
SELECT @Certificates + WHERE 
ELEcertificate_expiry_date = datepickerfilter1 OR EMLcertificate_expiry_date = datepickerfilter1 OR EPCcertificate_expiry_date = datepickerfilter1 OR FIRcertificate_expiry_date = datepickerfilter1 OR GAScertificate_expiry_date = datepickerfilter1 OR HMOcertificate_expiry_date = datepickerfilter1 OR INScertificate_expiry_date = datepickerfilter1;
PREPARE smtp FROM @Certificates;
EXECUTE smtp;
END IF;
IF datepickerfilter1 = 0 AND datepickerfilter2 != 0 THEN
SELECT @Certificates + WHERE 
ELEcertificate_expiry_date = datepickerfilter2 OR EMLcertificate_expiry_date = datepickerfilter2 OR EPCcertificate_expiry_date = datepickerfilter2 OR FIRcertificate_expiry_date = datepickerfilter2 OR GAScertificate_expiry_date = datepickerfilter2 OR HMOcertificate_expiry_date = datepickerfilter2 OR INScertificate_expiry_date = datepickerfilter2;
PREPARE smtp FROM @Certificates;
EXECUTE smtp;
END IF;
IF datepickerfilter1 = 0 AND datepickerfilter2 != 0 THEN
SELECT @Certificates;
PREPARE smtp FROM @Certificates;
EXECUTE smtp;
END IF;
END

In this I'm trying store my previous result in one variable and then applying IF condition. But I'm failed to execute the procedure.

NOTE:

I've 7 different expiry_date columns in single table, by applying UNION ALL I convert it into single column. Now I'm trying to filter the data by passing parameters to resulted output.

I'm googled it, but not getting proper solution, any kind of help is welcome, thanks in advance.

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
Ganesh Aher
  • 1,118
  • 3
  • 21
  • 51

1 Answers1

0

The local variables (begins with @) are not evaluated at run time, you must use a regular string variable to store the SQL before create the prepared statement.

Build your @certificates variable usign concat(...) like this:

IF datepickerfilter1 <> 0 AND datepickerfilter2 = 0 THEN

   set @Certificates = concat(@Certificates,
         " WHERE ELEcertificate_expiry_date ", 
         " BETWEEN '",datepickerfilter1,"' AND '",datepickerfilter2, "'" );
     /*I'm using one line as example, you must complete to meet your query*/

   PREPARE smtp FROM @Certificates;
   EXECUTE smtp;
   DEALLOCATE PREPARE smtp; -- important
END IF;

Do the same to the other prepared statements.

Ivan Cachicatari
  • 4,212
  • 2
  • 21
  • 41