0

I am using SQL Server 2012, and PHP 7.4. I have a stored procedure called temp that returns a number rounded to 2 decimal places.

CREATE PROCEDURE temp AS
SELECT
    ROUND(t.Real_Weight_Per_Part, 2) as realWeightPerPart
FROM t

EXEC temp

The result:

enter image description here

Which is what I expect. However, when I call this stored procedure from PHP like so:

$sql = "EXEC temp";
$stmt = $conn->prepare($sql);
$stmt->execute();

$data = $stmt->fetchAll(\PDO::FETCH_ASSOC);

I get the following response in $data:

[
    {"realWeightPerPart": "11.359999999999999"},
    {"realWeightPerPart": "60.5"},
    {"realWeightPerPart": "11.359999999999999"},
    ...
]

Why is it that when I am in the SQL Server window and I execute the stored procedure, I get the rounded results, but when I execute it from PHP I get the unrounded results?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Michael
  • 3,093
  • 7
  • 39
  • 83
  • What datatype is `Real_Weight_Per_Part`? – Dale K Dec 01 '20 at 05:23
  • 1
    My bet is the driver converting to float and that's why you get this result. You may need to iterate each value and round again in PHP, or use PDO::FETCH_FUNC with round function as parameter . – Felippe Duarte Dec 01 '20 at 05:29
  • 1
    Seems to be [a quirk in how floats are represented](https://bugs.php.net/bug.php?id=75920). Try `CAST(ROUND(t.Real_Weight_Per_Part, 2) AS VARCHAR(255))`. Also see https://stackoverflow.com/questions/23120584/why-does-mysql-round-floats-way-more-than-expected. It's written about MySQL but probably applies to SQL Server as well. – kmoser Dec 01 '20 at 05:30
  • 1
    Instead of casting to varchar, cast to decimal, floats cannot represent some numbers accurately. But a decimal can (I suspect you should be using decimal in the first place). – Dale K Dec 01 '20 at 05:46
  • Casting to decimal worked for me. Thanks for all your help! – Michael Dec 01 '20 at 06:18

0 Answers0