Whenever large numbers are multiplied in SQL Server, a loss of precision may occur. I am working with a database that makes extensive usage of the type DECIMAL(38, 18) to hold large numbers. When multiplying two numbers, precision is loss in most cases because SQL Server internally truncates the numbers prior to multiplying them. This can often be frustrating, like in the following example:

DECLARE
  @d1 DECIMAL(38, 18) = 1234.123456789012345678,
  @d2 DECIMAL(38, 18) = 1.0
SELECT CONVERT(DECIMAL(38, 18), @d1 * @d2)

The expected result is obviously 1234.123456789012345678, but SQL Server displays 1234.123457000000000000. While we all agree that multiplying large numbers often implies some kind of truncation, the following function returns results that are by far more precise than the default behavior of SQL Server.

CREATE FUNCTION dbo.DecimalMultiply(@d1 DECIMAL(38, 18), @d2 DECIMAL(38, 18))
RETURNS DECIMAL(38, 18) AS BEGIN
--Test for trivial cases
IF @d1 IS NULL OR @d2 IS NULL RETURN NULL
IF @d1 = 0 OR @d2 = 0 RETURN 0
IF @d1 = 1 RETURN @d2
IF @d2 = 1 RETURN @d1
--Get integer and fractional part
DECLARE
  @pl1 VARCHAR(MAX) = PARSENAME(@d1, 2),
  @pr1 VARCHAR(MAX) = REPLACE(RTRIM(REPLACE(PARSENAME(@d1, 1), '0', ' ')), ' ', '0'),
  @pl2 VARCHAR(MAX) = PARSENAME(@d2, 2),
  @pr2 VARCHAR(MAX) = REPLACE(RTRIM(REPLACE(PARSENAME(@d2, 1), '0', ' ')), ' ', '0')
--Test for overflow
IF LEN(@pl1) + LEN(@pl2) > 20 RETURN 99999999999999999999.999999999999999999
--Test for cases where @d1 and @d2 are small enough so the result will not be truncated.
IF LEN(@pl1) <= 10 AND LEN(@pr1) <= 9 AND LEN(@pl2) <= 10 AND LEN(@pr2) <= 9
  RETURN CONVERT(DECIMAL(38, 18), CONVERT(DECIMAL(19, 9), @d1) * CONVERT(DECIMAL(19, 9), @d2))
IF LEN(@pl1) <= 5 AND LEN(@pr1) <= 9 AND LEN(@pl2) <= 15 AND LEN(@pr2) <= 9
  RETURN CONVERT(DECIMAL(38, 18), CONVERT(DECIMAL(14, 9), @d1) * CONVERT(DECIMAL(24, 9), @d2))
IF LEN(@pl1) <= 15 AND LEN(@pr1) <= 9 AND LEN(@pl2) <= 5 AND LEN(@pr2) <= 9
  RETURN CONVERT(DECIMAL(38, 18), CONVERT(DECIMAL(24, 9), @d1) * CONVERT(DECIMAL(14, 9), @d2))
--Mathematical formula "(a + b)(c + d) = ac + ad + bc + bd" allows us to process integer and fractional part separately
--For example, 2.34 * 3.45 = 2 * 3 + 2 * 0.45 + 0.34 * 3 + 0.34 * 0.45 = 6 + 0.9 + 1.02 + 0.153 = 8.073
--Sign will be reapplied at the end
DECLARE @sign INT = SIGN(@d1) * SIGN(@d2)
SET @d1 = ABS(@d1)
SET @d2 = ABS(@d2)
DECLARE @d1i DECIMAL(20, 0) = CONVERT(DECIMAL(20, 0), FLOOR(@d1))
DECLARE @d1f DECIMAL(18, 18) = CONVERT(DECIMAL(18, 18), @d1 - @d1i)
DECLARE @d2i DECIMAL(20, 0) = CONVERT(DECIMAL(20, 0), FLOOR(@d2))
DECLARE @d2f DECIMAL(18, 18) = CONVERT(DECIMAL(18, 18), @d2 - @d2i)
DECLARE @dr DECIMAL(38, 18) = CONVERT(DECIMAL(38, 18), @d1i * @d2i) + CONVERT(DECIMAL(38, 18), @d1i * @d2f) + CONVERT(DECIMAL(38, 18), @d1f * @d2i) + CONVERT(DECIMAL(38, 18), @d1f * @d2f)
RETURN IIF(@sign = -1, -@dr, @dr)
END

After eliminating trivial cases, the function uses PARSENAME to get the left and right digits of the arguments. Then, if the scale of these numbers is small enough, it quickly multiplies using the * operator of SQL Server. Otherwise, it uses the mathematical formula (a + b)(c + d) = ac + ad + bc + bd to transform one large multiplication into a sum of four mid-scale multiplications. The types used in the function (DECIMAL(38, 18), DECIMAL(20, 0), DECIMAL(18, 18)) reflect the types used in my database. You may adjust them to your needs.

We can now re-execute our example.

DECLARE
  @d1 DECIMAL(38, 18) = 1234.123456789012345678,
  @d2 DECIMAL(38, 18) = 1.0
SELECT dbo.DecimalMultiply(@d1, @d2)

and the result is 1234.123456789012345678 because a trivial case was detected. This last example better tests our function:

DECLARE
  @d1 DECIMAL(38, 18) = 1234.123456789012345678,
  @d2 DECIMAL(38, 18) = 1.1
SELECT dbo.DecimalMultiply(@d1, @d2)

And the result is: 1357.535802467913580248 where using the multiplication operator returns 1357.535802000000000000.