SQL Function to convert Float to VarChar

This is related to the Google Maps web part from an earlier post. I have latitude and longitude columns in a database table. Both are Float datatypes. I needed to build a URL string for the push-pin description box. That would seem straight-forward enough, however, I was receiving an error on converting a float value to a varchar. Below is my first take on the SQL to create my location list. This location list is connected to Sharepoint as an external content type and external BCS list.

INSERT INTO LocationsPins (Title, Description, Latitude, Longitude)
Select Address as Title, ('URL: <a href="/sandbox/lists/locationdata/Read%20List.aspx?Lat='
+ Latitude + '">Location Detail</a>') AS Description, Latitude, Longitude

From LocationData
Where Latitude IS NOT NULL
and Latitude > 0
AND Longitude IS NOT NULL
AND Address IS NOT NULL
AND City is NOT NULL
AND State is NOT NULL
AND Zip is NOT NULL
GROUP BY Address, Latitude, Longitude

That by itself threw the error regarding converting a float to varchar. Using Convert or Cast was not getting what I needed as Latitude can be 6 or more decimal places and those functions would add trailing Zeroes.

So, I had to create a Function that would take this into account.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FloatConvert]
(@ParmValue Float)
RETURNS VARCHAR(25)
AS
BEGIN
DECLARE @RetValue VARCHAR(25)
SET @RetValue = CAST(CAST(@ParmValue AS DECIMAL(15,7)) AS VARCHAR(20))
SET @RetValue = REVERSE(@RetValue)
WHILE SUBSTRING(@RetValue,1,1) = 0
BEGIN
SET @RetValue = SUBSTRING(@RetValue,2,25)
END
SET @RetValue = REVERSE(@RetValue)
RETURN @RetValue
END

The SQL query (description field) to build the list looks like this;

Select Address as Title, (‘URL: <a href=”/sandbox/lists/locationdata/Read%20List.aspx?Lat=’+ dbo.FloatConvert(Latitude) + ‘”>Location Detail</a>’) AS Description, Latitude, Longitude

Hope this helps someone as I’ve seen postings regarding this conversion during some Google searches.