Skip navigation links

MySQL Forums :: Stored Procedures :: string concatenation in stored procedures


Advanced Search

string concatenation in stored procedures
Posted by: sajid kamal ()
Date: February 16, 2010 03:06AM

hi,

i have problem. i am wring a stored procedure in mysql for my customer sale information. i want daily sale items in one string my procedure is

-- --------------------------- my stored procedures started --------------------
CREATE PROCEDURE dbshop.`proDailySaleDetails`(IN _cID VARCHAR(5), OUT ItemDesc varchar(200) charset utf8)

BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE lSaleDesc varchar(200) charset utf8;
DECLARE _CustomerID,_eDate,_pTitle,_Packing,_SAmountType,_Qty,_Rate,_CommAmount,_TotalAmount,_RecivedAmount,_tKey varchar(100) charset utf8;
DECLARE cCustomerSaleDetails CURSOR FOR
Select tblsaledetails.CustomerID, tblsalepurchasetran.eDate, tblproducts.pTitle, tblproducts.Packing,tblproducts.SAmountType, tblsaledetails.Qty, tblsaledetails.Rate, tblsaledetails.CommAmount, tblsaledetails.TotlaAmount, ifnull(tblsaledetails.RecivedAmount,0) as RecivedAmount ,tblsaledetails.tKey From tblsalepurchasetran Inner Join tblsaledetails On tblsaledetails.sKey = tblsalepurchasetran.ID Inner Join tblproducts On tblsalepurchasetran.iID = tblproducts.ID Where(tblsaledetails.CustomerID =_cID and tblsalepurchasetran.eDate=curdate()) Order By tblsaledetails.ID;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;

OPEN cCustomerSaleDetails;

the_loop: LOOP
SET lSaleDesc=NULL;

FETCH cCustomerSaleDetails INTO _CustomerID,_eDate,_pTitle,_Packing,_SAmountType,_Qty,_Rate,_CommAmount,_TotalAmount,_RecivedAmount,_tKey;
IF no_more_rows THEN
-- CLOSE cCustomerSaleDetails;
LEAVE the_loop;
END IF;

SET ItemDesc= CONCAT('/',_pTitle ,' ',_Packing,_Qty);

END LOOP the_loop;
close cCustomerSaleDetails;
END;

-- --------------------------- my stored procedures Ended --------------------
i have many record in my tables and my procedures cursor fetch all records but when i try to concatenation record it give me null value

"i need all Item name in one string."

this line work well
SET ItemDesc= CONCAT('/',_pTitle ,' ',_Packing,_Qty);

but when am try to add other line in string
it give a null value

SET ItemDesc= CONCAT(ItemDesc,'/',_pTitle ,' ',_Packing,_Qty);

i think am am not know much about mysql and my concatenation is wrong.
please help me what i am do for correct results.

Options: ReplyQuote


Subject Views Written By Posted
string concatenation in stored procedures 12414 sajid kamal 02/16/2010 03:06AM
Re: string concatenation in stored procedures 2349 Devart Team 02/16/2010 04:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.