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


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)

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;

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;

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

END LOOP the_loop;
close cCustomerSaleDetails;

-- --------------------------- 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 11153 sajid kamal 02/16/2010 03:06AM
Re: string concatenation in stored procedures 2209 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.