Error converting to float 1

[christer102]

Getting error converting to float when comparing (greater than) two values although both values are float. I have even tried to convert to values to float, and still getting the error (see down at the bottom).


CREATE PROCEDURE dbo.SetNTP as
SET QUOTED_IDENTIFIER OFF
declare @SQL varchar(8000), @minprem2 FLOAT, @premval VARCHAR(250)
select @SQL=QUERYNTP1, @minprem2=CONVERT(float,isnull(MINPREM2,0) ) FROM ATMCONFIG
SET @premval =' AND (convert(float,TOTCLIENTPREM)>convert(float,'+@minprem2+'))'
SET @SQL='UPDATE ACCOUNTINFO SET NTP = '+@SQL+ ' where ((MANAUT=1) or (MANAUT is null)) ' + @premval

EXEC(@SQL)
GO


Server: Msg 8114, Level 16, State 5, Procedure SetNTP, Line 5
Error converting data type varchar to float.

 

[ESquared]

How about doing a PRINT @SQL before the EXEC and showing it here so we can see it?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[christer102]

Tried that but the error happens before the execution. It appears that the error happens at the SET @SQL line

 

[SQLSister]

use this instead
SET @premval =' AND (convert(float,TOTCLIENTPREM)>convert(float,'+cast(@minprem2 as varchar(100))+'))'

Adjust your varchar as needed. By the way, you should get away from using float. It is not an exact datatype and will casue rounding problems eventually.

"NOTHING is more important in a database than integrity." ESquared

 

[christer102]

Works. Thank you.

 

[ESquared]

Getting error converting to float when comparing ...

No you're not. You're getting an error converting to float when assigning a value to the variable @SQL. You aren't comparing anything yet because you haven't run the SQL statement you're trying to build.

Thus SQLSister's code to convert the float to a varchar so it can become part of a SQL string is right.

But why are you using dynamic SQL at all? Can't you do your task without it?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]