0

I have a stored procedure that has input params:

  @Latitude float
, @Longitude float

but when I pass these values:

 @Latitude=-28.640328248358344
,@Longitude=153.61249352645871

the values being stored in the table are:

Lat: -28.6403
Lng: 153.612

If I do a standard insert or update on the table the values are correct.

I tried changing the params to float(54) but it made no difference... The reason I am using a float is because the Lat/Lng are actually computed cols from a geography column (and it states in the designer that they are floats) - I have double checked that it's not related to the computed cols by inserting the lat/lng values from the stored procedure into a couple of varchar columns.

I am using EF and can confirm that the values are correct in the Entities and I have checked the SQL Profiler to confirm that the full values are included in the exec call.

EDIT: I insert these values into a column call GeoLocation of type geography:

GeoLocation = geography::STPointFromText('POINT(' + CONVERT(varchar, @Longitude) + ' ' + CONVERT(varchar, @Latitude) + ')',4326)
Rob
  • 10,004
  • 5
  • 61
  • 91
  • @marc_s - my question clearly states that I use a geography column and in my SP I have --- GeoLocation = geography::STPointFromText('POINT(' + CONVERT(varchar, @Longitude) + ' ' + CONVERT(varchar, @Latitude) + ')',4326) --- but this is irrelevant as I also mention that I stored the raw @lat/lngs in a varchar col to see what they look like and they are also truncated... – Rob Apr 17 '11 at 11:08
  • I agree with marc_s: from your question, and even your clarification above, the datatype of the target column is unclear. You say you get your values FROM a geography column, and have inserted them for testing purposes into varchar columns; but what is the datatype of the column where the values appear to be truncated? "...the values being stored in the table..." -- – Tim Apr 17 '11 at 11:13
  • Both the Computed Float columns and the Varchar Cols (that I did an additional test on) are truncated – Rob Apr 17 '11 at 11:15
  • 1
    Just incase your checking these results with PRINT, PRINT always truncates floats to 4 dp. – Alex K. Apr 17 '11 at 11:16

2 Answers2

3

When you cast float to varchar you lose accuracy. Use STR to convert to varchar

A comment says "CONVERT(varchar, @Longitude)". This isn't "truncating input" but normal behaviour

gbn
  • 422,506
  • 82
  • 585
  • 676
1

Try this:

DECLARE @Longitude FLOAT = 41.1234567

SELECT CAST(@Longitude AS VARCHAR(24))

What is your result?? In my case, it seems that SQL Server will always truncate to four digits after the decimal point.....

COuld that be the cause of your truncation?? It's really only the conversion to VARCHAR that causes truncation?? Try storing the values in your sproc as FLOAT and see if those values are truncated, too - I'm pretty sure they won't be!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459