MySQL хранящаяся процедура Параметр ⇐ MySql

[Anonymous]

Следующая сохраненная процедура - это преобразование параметра ThisAmount , который, например, я предоставляю 2387.97, раундами в 2388, но он объявляется в столбце, и в списке параметров является десятичным, и, наконец, я попытался сыграть как десятичное, и до сих пор не повезло. Я не могу понять это. Я искал, искал и пробовал много разных вариантов, но безрезультатно. Процедура работает, за исключением проблемы округления. Это не первый раз, когда это произошло, поэтому я подумал, что спрошу кого -то с большим знанием, чем у меня. Любая помощь будет высоко оценена. < /P>
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


Подробнее здесь: https://stackoverflow.com/questions/797 ... ng-rounded