How come my stored procedure does not concatenate strings like normal queries?

Programming in mysql: How come my stored procedure does not concatenate strings like normal queries? on newest questions tagged mysql – Stack Overflow

Full example is provided for you to try to solve.
The problem is I want to add two columns (‘MUNTAX’ AND ‘SCHTAX’) and put the result into a third column (‘TOTALTAX’) in the table (‘house’). However, the columns are prices of type varchar not integer, so I have to remove the ‘$’ sign and ‘,’ before calculating. And after calculation, must convert back to varchar the total adding back in the ‘$’ and ‘,’.

Where I’m hung up is on getting the total.

Create the table:

CREATE TABLE `house` (
`ID` int(10) unsigned NOT NULL auto_increment,
`MLS_NO` int(10) default NULL,
`AGENT` varchar(120) default NULL,
`DISP_ORDER` int(4) default NULL,
`ADDR` varchar(200) NOT NULL default '',
`PRICE` varchar(12) NOT NULL default '',
`PRTYPE` varchar(20) default NULL,
`BUILTYPE` varchar(30) default NULL,
`YEAR` varchar(4) default NULL,
`LAREA` decimal(10,0) default NULL,
`ROOM` decimal(10,0) default NULL,
`BEDROOM` decimal(10,0) default NULL,
`BATHROOM` decimal(10,0) default NULL,
`PWDRROOM` decimal(10,0) default NULL,
`GARAGE` decimal(10,0) default NULL,
`PARKING` decimal(10,0) default NULL,
`MUNTAX` varchar(30) default NULL,
`SCHTAX` varchar(30) default NULL,
`TOTALTAX` varchar(30) default NULL,
`DESCR` text,
`DEFAULTPIC` decimal(10,0) default NULL,
`ADTYPE` varchar(100) default NULL,
`DESCR_2` text,
PRIMARY KEY  (`ID`),
KEY `Index_2` (`ADDR`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=122 ;

INSERT INTO `house` (`ID`, `MLS_NO`, `AGENT`, `DISP_ORDER`, `ADDR`, `PRICE`, `PRTYPE`, `BUILTYPE`, `YEAR`, `LAREA`, `ROOM`, `BEDROOM`, `BATHROOM`, `PWDRROOM`, `GARAGE`, `PARKING`, `MUNTAX`, `SCHTAX`, `TOTALTAX`, `DESCR`, `DEFAULTPIC`, `ADTYPE`, `DESCR_2`) VALUES (110, 8620825, 'N', NULL, '5871 FL', '$1,025,000.0', 'DETACHED', 'COTTAGE', NULL, '589', '10', '4', '3', '1', NULL, '4', '$7,774.42', NULL, 'NULL', 'Beautiful');

The stored procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS TotalTaxProc $$

CREATE PROCEDURE TotalTaxProc(IN MLS INT(10), OUT MUNFLO FLOAT(10), OUT SCHINT FLOAT(10), OUT TOTTAX VARCHAR(10))
BEGIN
DECLARE SCH VARCHAR(30);
DECLARE MUN VARCHAR(30);

SET SCHFLO=0.0;
SET MUNFLO=0.0;
SET TOTTAX='';

SELECT MUNTAX FROM house WHERE MLS_NO=MLS INTO MUN;
SELECT SCHTAX FROM house WHERE MLS_NO=MLS INTO SCH;

SET MUNFLO=IFNULL(REPLACE(REPLACE(MUN, '$', ''), ',', ''), 0);
SET SCHFLO=IFNULL(REPLACE(REPLACE(SCH, '$', ''), ',', ''), 0);

SET TOTTAX=CONCAT('$', MUNFLO + SCHFLO ) ;

SET TOTTAX=CONCAT(SUBSTRING_INDEX(TOTTAX, SUBSTRING(TOTTAX, -6), 1),',',SUBSTRING(TOTTAX, -6) );

UPDATE house SET TOTALTAX=TOTTAX WHERE MLS_NO=MLS;
END $$
DELIMITER ;

And finally the call,

CALL TotalTaxProc(8620825,@MUNINT,@SCHINT,@TOTTAX);
SELECT @MUNINT,@SCHINT,@TOTTAX;

So obviously I get just the comma, where the TOTTAX should be giving ‘$7,774.42′

source: http://stackoverflow.com/questions/11568626/how-come-my-stored-procedure-does-not-concatenate-strings-like-normal-queries
Programming in mysql: programming-in-mysql