Using the POWER() Function with a Negative Exponent

I'm having trouble using the POWER() function. Two integer columns hold a value and an exponent respectively. When I write a query that uses a negative exponent, the query returns 0. For example, the following query (which for simplification uses only constants):

SELECT 2 * CAST(POWER(10, -2)AS DECIMAL(9,3))

returns .000. I expected the query to return .020. Why didn't the query return the expected result?

To answer your question, let's first look at an excerpt from SQL Server Books Online's (BOL) description of the way the POWER() and EXP() exponential functions work:

"The POWER function returns the value of the given numeric expression to the specified power. POWER(2,3) returns 2 to the third power, or the value 8. Negative powers can be specified, so POWER(2.000, -3) returns 0.125. Notice that the result of POWER(2, -3) is 0. This is because the result is the same data type as the given numeric expression. Therefore, if the result has three decimal places, the number to raise to a given power must have three decimals, too."

Applying that explanation to your problem, you need to convert the POWER function's first argument to a decimal data type. To perform the conversion, you can choose either of the following alternatives:

SELECT 2 * POWER(CAST(10 AS DECIMAL(9, 3)), -2)

or

SELECT 2 * POWER(10 * 1.000, -2)

That way, you apply the POWER() function to a decimal so that you can return a decimal.