SQL Server Float Datatype out of range

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.

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.

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