Date Parsing in Stored Procedure
Hi
I am not able to parse date parameters (Fromdate and Todate) from calling procedure to stored procedure definition
Calling Procedure:
call TrackingRegisterReport('Ilmenite',${CountryParam},${PortParam},${CustomerParam},${FromDateParam},${ToDateParam})
Stored Procedure:
Create PROCEDURE TrackingRegisterReport (IN ProductName VARCHAR(255), IN CountryName VARCHAR(255), IN PortName VARCHAR(100), IN CustomerName VARCHAR(255), IN FromDate DATE, IN ToDate DATE)
BEGIN
SET SESSION group_concat_max_len=100000;
SET @cols = (SELECT GROUP_CONCAT('sum(CASE WHEN pr.Grade_Name =','"',Grade_Name,'"',' THEN inv.Inv_Qty END) AS "',Grade_Name SEPARATOR '",') FROM D_ODS_Product where Active='Y' and Product_Name=ProductName order by Grade_Name);
SET @product=ProductName;
SET @country=CountryName;
SET @Customer=CustomerName;
SET @port=PortName;
SET @sql = CONCAT('Select
po.PO_No,
po.PO_Name,
po.PO_Date,
po.PO_Status,
concat(MONTHNAME(po.PO_Date),"-",YEAR(po.PO_Date)) as PO_Month,
po.Delivery_Port,
po.Shipping_Country,
cus.Customer_Name,
pr.Product_Name,
pr.Grade_Name,
sum(po.PO_Qty) as OrderedQty,
sum(inv.FOB_Val) as FOB_Val,
sum(inv.CF_Val) as CFR,
sum(inv.Inv_Qty) as InvoicedQty,
inv.Payment_Terms,
inv.Packing_Type,
cs.Stuffing_Date,
sh.Line_Name as Shipping_Line,
inv.Inv_No,
inv.Inv_Date, ',@cols,'" from
F_ODS_PurchaseOrd po join D_ODS_Customer cus on (po.Customer_ID=cus.D_Customer_ID and po.Active="Y" )
join D_ODS_Product pr on (po.Grade_ID=pr.Grade_ID and po.Product_ID=pr.D_Product_ID)
Join F_ODS_Invoices inv on (po.PO_ID=inv.PO_ID and po.Product_ID=inv.Product_ID and po.Grade_ID=inv.Grade_ID and inv.Active="Y")
Left join F_ODS_ContainerStuff_QC cs on (inv.Inv_No=cs.Invoice_No and po.PO_No=cs.PO_No and po.Product_ID=cs.Product_ID and po.Grade_ID=cs.Grade_ID and cs.Active="Y")
Left join F_ODS_Shipment sh on (sh.ContainerStuffing_ID=cs.ContainerStuffing_ID and sh.Active="Y")
where pr.Product_Name="',@product,'"
and (cus.Customer_Name="',@customer,'" or "ALL"="',@customer,'")
and (po.Shipping_Country="',@country,'" or "ALL"="',@country,'")
and (po.Delivery_Port="',@port,'" or "ALL"="',@port,'")
and date(po.PO_Date) between ',FromDate,' and ',ToDate,'
Group by
pr.Product_Name,
pr.Grade_Name,
po.PO_No,po.PO_Name,po.PO_Date,po.PO_Status,po.PO_Date,po.Delivery_Port,
po.Shipping_Country,cus.Customer_Name,inv.Payment_Terms,
inv.Packing_Type,cs.Stuffing_Date,sh.Line_Name,inv.Inv_No,inv.Inv_Date
order by cus.Customer_Name,pr.Product_Name,pr.Grade_Name,po.PO_Date,inv.Inv_Date,po.PO_Name,inv.Inv_No');
PREPARE stmt FROM @sql;
EXECUTE stmt;
END;
Anyone help me to troubleshoot the issue
Thanks & Regards
Thirumurugan