CODEWITHSUNDEEP
sql-serverprecisionsqldatatypes
JoeLeBaron
JoeLeBaron

Reputation: 348

SQL Server Float Datatype out of range

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.

Upvotes: 10

Views: 3411

Answers (3)

JoeLeBaron
JoeLeBaron

Reputation: 348

Details on what I did to proof out the accepted answer above.

I was taking the trace statement and replaying it on another DB using C# like this (this will throw the out of range error):

cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "exec sp_executesql @stmt=N'SELECT @f',
    @params=N'@f float',@f=120700.800000000002910383045673370361328125";
cmd.ExecuteNonQuery();

What I need to do is extract those parts and build a stored procedure with parameters like this :

cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "sp_executesql";

SqlParameter p1 = new SqlParameter();
p1.DbType = System.Data.DbType.String;
p1.ParameterName = "@stmt";
p1.Value = "SELECT @f";

SqlParameter p2 = new SqlParameter();
p2.DbType = System.Data.DbType.String;
p2.ParameterName = "@params";
p2.Value = "@f float";

SqlParameter p3 = new SqlParameter();
p3.DbType = System.Data.DbType.Double;
p3.Value = 120700.8000000000000000000000000000000000000000000000;
p3.ParameterName = "@f";

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);

cmd.ExecuteNonQuery();

When I run that, it shows up in the trace as:

exec sp_executesql @stmt=N'SELECT @f',@params=N'@f float',@f=120700.800000000002910383045673370361328125

So the issue is that I cannot just take the statement from an rpc_completed event. I need to parse it and reconstruct the stored procedure explicitly.

Upvotes: 2

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

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.

Upvotes: 3

Alex
Alex

Reputation: 5157

The string literal 120700.8000000000000000000000000000000000000000000000 is first implicitly converted to decimal, hence the error.

Try

DECLARE @variable float = 120700.8000000000000000000000000000000000000000000000E0

Source: TSQL - make a literal float value

Upvotes: 3

Related Questions