The following stored procedure is converting the thisAmount parameter, which for example I provide 2387.97, rounds to 2388, but it is declared in the column, and within the parameter list to be a decimal, and finally I tried to cast as a decimal, and still no luck. I can't figure this out. I have searched and searched and tried many different options, but to no avail. The procedure works, except for the rounding issue. This isn't the first time this has happened, so I figured I would ask someone with more knowledge than me. Any help would be greatly appreciated.
CREATE DEFINER=`myUserName`@`%` PROCEDURE `billing_addOCIPTrueUp`(IN `thisQuoteID` BIGINT,IN `thisAmount` DECIMAL, IN `thisTable` BLOB, IN `thisDetailTable` BLOB)
BEGIN
SET @thisAmount = CAST(thisAmount AS DECIMAL(10,2));
SET @newJobName = (SELECT JobName FROM Quotes WHERE QuoteID = thisQuoteID);
SET @q = CONCAT('INSERT INTO ', thisTable, ' (QuoteID, ClientID, PrimaryClientContactID, PrimaryJobContactID, ParentQuoteID, QuoteDate, BidDueDate, DateSent, QuoteTotal, JobName, `Status`, ApprovalStatus, PmtStatus, WrapType, QuoteType, Retention, ClosingDay)
SELECT ',
thisTable, '.QuoteID + 1 AS QuoteID,',
thisTable, '.ClientID,',
thisTable, '.PrimaryClientContactID,',
thisTable, '.PrimaryJobContactID,',
thisTable, '.ParentQuoteID,
DATE(NOW()) AS QuoteDate,',
thisTable, '.BidDueDate,',
thisTable, '.DateSent,',
@thisAmount, ' AS QuoteTotal, "',
@newJobName, ' y||y Insurance Deduct" AS JobName,
3 AS `Status`,
2 AS ApprovalStatus,',
thisTable, '.PmtStatus,',
thisTable, '.WrapType,
14 AS QuoteType,',
thisTable, '.Retention,',
thisTable, '.ClosingDay
FROM
', thisTable, '
WHERE
', thisTable, '.QuoteID LIKE "', thisQuoteID,'00%"
ORDER BY
', thisTable, '.QuoteID DESC
LIMIT 1;
INSERT INTO ', thisDetailTable, '(QuoteID, Heading, Description, Qty, Rate, Total, Misc, Variable, Color, Production, PlanPageNumber, DetailPageNumber, DetailNumber, Draw, DrawApproved, Measured, Mat, Cut, Fab, Coat, Delivered, Installed, ItemType, ItemTypeStyle, InQueue)VALUES(
(
SELECT IFNULL((SELECT '
, thisTable, '.QuoteID + 1 AS QuoteID
FROM ',
thisTable,
' WHERE
', thisTable,'.QuoteID LIKE "', thisQuoteID, '10%"
ORDER BY
', thisTable,'.QuoteID DESC
LIMIT 1), ', thisQuoteID, '1001)
),
"OCIP Insurance Deduction",
"",
1,',
thisAmount, ',',
thisAmount, ',
0.00,
0.00,
"n/a",
0,
"",
"",
"",
0,
0,
0,
0,
0,
0,
0,
0,
0,
100,
NULL,
0
)');
SELECT CAST(@q AS CHAR(10000) CHARACTER SET utf8) AS text;
#PREPARE stmt FROM @q;
#EXECUTE stmt;
#DEALLOCATE PREPARE stmt;
END
CREATE DEFINER=`tigtech_admin`@`%` PROCEDURE `billing_addOCIPTrueUp`(IN `thisQuoteID` BIGINT,IN `thisAmount` DECIMAL, IN `thisTable` BLOB, IN `thisDetailTable` BLOB)
BEGIN
SET @thisAmount = CAST(thisAmount AS DECIMAL(10,2));
SET @newJobName = (SELECT JobName FROM Quotes WHERE QuoteID = thisQuoteID);
SET @q = CONCAT('INSERT INTO ', thisTable, ' (QuoteID, ClientID, PrimaryClientContactID, PrimaryJobContactID, ParentQuoteID, QuoteDate, BidDueDate, DateSent, QuoteTotal, JobName, `Status`, ApprovalStatus, PmtStatus, WrapType, QuoteType, Retention, ClosingDay)
SELECT ',
thisTable, '.QuoteID + 1 AS QuoteID,',
thisTable, '.ClientID,',
thisTable, '.PrimaryClientContactID,',
thisTable, '.PrimaryJobContactID,',
thisTable, '.ParentQuoteID,
DATE(NOW()) AS QuoteDate,',
thisTable, '.BidDueDate,',
thisTable, '.DateSent,',
@thisAmount, ' AS QuoteTotal, "',
@newJobName, ' y||y Insurance Deduct" AS JobName,
3 AS `Status`,
2 AS ApprovalStatus,',
thisTable, '.PmtStatus,',
thisTable, '.WrapType,
14 AS QuoteType,',
thisTable, '.Retention,',
thisTable, '.ClosingDay
FROM
', thisTable, '
WHERE
', thisTable, '.QuoteID LIKE "', thisQuoteID,'00%"
ORDER BY
', thisTable, '.QuoteID DESC
LIMIT 1;
INSERT INTO ', thisDetailTable, '(QuoteID, Heading, Description, Qty, Rate, Total, Misc, Variable, Color, Production, PlanPageNumber, DetailPageNumber, DetailNumber, Draw, DrawApproved, Measured, Mat, Cut, Fab, Coat, Delivered, Installed, ItemType, ItemTypeStyle, InQueue)VALUES(
(
SELECT IFNULL((SELECT '
, thisTable, '.QuoteID + 1 AS QuoteID
FROM ',
thisTable,
' WHERE
', thisTable,'.QuoteID LIKE "', thisQuoteID, '10%"
ORDER BY
', thisTable,'.QuoteID DESC
LIMIT 1), ', thisQuoteID, '1001)
),
"OCIP Insurance Deduction",
"",
1,',
thisAmount, ',',
thisAmount, ',
0.00,
0.00,
"n/a",
0,
"",
"",
"",
0,
0,
0,
0,
0,
0,
0,
0,
0,
100,
NULL,
0
)');
#PREPARE stmt FROM @q;
#EXECUTE stmt;
#DEALLOCATE PREPARE stmt;
END
In the stored procedure's definition thisAmount variable is declared simply as decimal, without specifying the max digit and decimal digit lengths.
According to mysql documentaion on decimal data type:
The declaration syntax for a
DECIMALcolumn isDECIMAL(M,D). The ranges of values for the arguments are as follows:
Mis the maximum number of digits (the precision). It has a range of 1 to 65.
Dis the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger thanM.If
Dis omitted, the default is 0. IfMis omitted, the default is 10.
The emphasis is mine.
Change thisAmount variable's definition to decimal(10,2) and you will not lose the decimal digits.