Float(53) is not double precision when in declared variable?

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 16, 2004 at 5:26 pm

  #61370

  I have a stored procedure that declares a float(53) variable and sets a float value = to it.  The problem is, even though the variable is declared with double precision (float(53)), which is supposed to be 15 significant digits, when the variable is used in dynamic SQL (which I need to use here) the data gets mangled into single precision (float(24)), with only 7 significant digits. 

  The simplified version of this is such:

  DECLARE @a as float(53)

  DECLARE @DynSQL varchar(1000)

  SELECT @a = 367970.45

  SELECT @DynSQL = 'SELECT ' + cast(@a as varchar(50))

  SELECT @a

  EXEC (@DynSQL)

   

  Notice that the SELECT returns: 

  367970.45000000001

  Notice that the EXEC(@DynSQL) returns: 

  367970

   

  WTF?  Any ideas?

  
  
  A.J.
  DBA with an attitude

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  November 16, 2004 at 5:52 pm

  #530385

  I think it is the cast to varchar where you are losing data. Try using the STR() function:

  DECLARE @a as float(53)

  DECLARE @DynSQL1 varchar(1000), @DynSQL2 varchar(1000)

  SELECT @a = 367970.45

  SELECT @DynSQL1 = 'SELECT ' + cast(@a as varchar(50))

  SELECT @DynSQL2 = 'SELECT ' + str(@a,50,11)

  SELECT @a

  EXEC (@DynSQL1)

  EXEC (@DynSQL2)

  Phil

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 16, 2004 at 6:15 pm

  #530386

  No it's not it...

  Run this instead to see the difference between the printed value and the selected value

   

  DECLARE @a as float(53)

  SELECT @a = 367970.45

  print @a

  SELECT @a

  
  
  A.J.
  DBA with an attitude

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 16, 2004 at 6:21 pm

  #530387

  Since your example works, then it must be that when you do a Print statement it is implicitly casting it to a varchar.  Does this sound right?

  
  
  A.J.
  DBA with an attitude

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  November 16, 2004 at 8:08 pm

  #530393

  Yes - Print does an implicit conversion to char or varchar if it can.

  Phil

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 17, 2004 at 8:46 am

  #530493

  Thanks Phil!  I appreciate your help.

  
  
  A.J.
  DBA with an attitude