SQL Server Float Datatype out of range

Question

I have a query that I captured through an extended events session with a float datatype that looks like :

@variable = 120700.8000000000000000000000000000000000000000000000

If I try to run the same query in SSMS, I get the error: The number '120700.8000000000000000000000000000000000000000000000' is out of the range for numeric representation (maximum precision 38).

Same as if you were to run

DECLARE @variable float = 120700.8000000000000000000000000000000000000000000000

The query succeeded when the trace was running. The Event is rpc_completed and it has a duration, cpu, rowcount, etc...

Not sure if this is relevant, but the query in question is an exec sp_executeSQL. The full query as captured by the trace looks like:

exec sp_executeSQL N'SELECT
    col1,
    col2
FROM table
WHERE col3 > @variable', N'@variable float', 
@variable = 120700.8000000000000000000000000000000000000000000000

So my question is, why is the code in the trace able to execute without error, but when I copy/paste that same code to SSMS it throws an error. If I take that same code and trim off a bunch of those zeros it works.

I am running on SQL Azure DB. I have reproduced on compatibility SQL2016, SQL2019, and on Prem SQL2019.

Answer 1

why is the code in the trace able to execute without error, but when I copy/paste that same code to SSMS it throws an error.

When you put this query in SSMS

exec sp_executeSQL N'SELECT
    col1,
    col2
FROM table
WHERE col3 > @variable', N'@variable float', 
@variable = 120700.8000000000000000000000000000000000000000000000

the literal 120700.8000000000000000000000000000000000000000000000 is interpreted as a numeric/decimal type. The engine tries to convert it to a value of numeric/decimal type and fails, because numeric has maximum precision of 38 digits.

If you put this query in SSMS, it should work

exec sp_executeSQL N'SELECT
    col1,
    col2
FROM table
WHERE col3 > @variable', N'@variable float', 
@variable = 120700.8000000000000000000000000000000000000000000000E0

I've added E0 at the end.

To make the float literal we need to use the scientific notation, as documented in Constants (Transact-SQL)

---

why is the code in the trace able to execute without error

I can't say for sure, but I suspect that the code that you see in the trace is not exactly the same code that was sent to the SQL Server by the application. It could have been sent in such a way that the parameter type and value was actually float, not a text string with all these zeroes.

I think, the application is likely to make an RPC call passing proper parameter with proper type to the function call. So, there was never a conversion from a text string to numeric or float type when an application made this successful call.