convert Float to Double? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

convert Float to Double?

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 05:32:02

Hi I have this sp which works fine, it calculates a distance and returns it as a float, for example:

0,210859566504426

Now when I request the value in vb.net as a double it does not return anything.

It only returns a value of -1,79769313486232E+308 when the distance in SQL is NULL.

Can anyone help me here?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 05:42:48

It only returns a value of -1,79769313486232E+308 when the distance in SQL is NULL.

when the values is null then replace it with some other values and then pass it the front end using IsNull function of SQL.

The Value which are getting is the junk value, its always better to initalize the variable to the default value when you declare it. In your case i guess you have not Intitialized the variable too.

Chirag

http://chirikworld.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 05:45:55

Hi, the sp is:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ZMGetPointsZoekenOpStraal]
(
@LATI float,
@LNG float,
@Distance int,
@NetwerkPZID int = -1)

AS
if @netwerkPZID = -1
BEGIN
select d.*
FROM (
SELECT
dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS Dist,
LocatieID,
LocatieNaam,
LocatieBezoekStraat,
LocatieBezoekNummer,
LocatieBezoekPlaatsnaam,
LocatieBezoekPostcode,
LocatieHoofdNetwerkID,
LocatieNetwerkPartner,
LocatieLon,
LocatieLat,
LocatieZoom
FROM
ZMLocatie
) d
WHERE
d.Dist < @Distance
ORDER BY d.Dist
END
else
Begin
select d.*
FROM (
SELECT
dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS Dist,
LocatieID,
LocatieNaam,
LocatieBezoekStraat,
LocatieBezoekNummer,
LocatieBezoekPlaatsnaam,
LocatieBezoekPostcode,
LocatieHoofdNetwerkID,
LocatieNetwerkPartner,
LocatieLon,
LocatieLat,
LocatieZoom
FROM
ZMLocatie
) d
WHERE
d.Dist < @Distance
AND
(LocatieHoofdNetwerkID = @NetwerkPZID)
or
EXISTS
(Select * from ZMproduct Where (ZMproduct.ProductLocatieID = LocatieID) AND Exists (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = ZMproduct.ProductID AND ZMNetwerkPZProduct.NetwerkPZID = @NetwerkPZID))
ORDER BY d.Dist
END

Where should I do the replace exactly?
At dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS Dist ?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 06:05:06

That's funny, I got it to, sort of, work, using COALESCE.
I did:

COALESCE(dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon), 1000) AS Dist,

So it will return 1000 when Null.
Only

d.Dist < @Distance

does not leave out the 1000's

And still, on the output side, I only see the 1000's, so it does not really solve anything.

Could it be that 0,210859566504426
cannot be converted to a double?

Anyone knows how to do this?
Perhaps cut of the last bit of the value like: 0,210859

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 06:11:46

[code]
ALTER PROCEDURE [dbo].[ZMGetPointsZoekenOpStraal]
(
@LATI float,
@LNG float,
@Distance int,
@NetwerkPZID int = -1)

AS
if @netwerkPZID = -1
BEGIN
select d.*
FROM (
SELECT
dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS Dist,
LocatieID,
LocatieNaam,
LocatieBezoekStraat,
LocatieBezoekNummer,
LocatieBezoekPlaatsnaam,
LocatieBezoekPostcode,
LocatieHoofdNetwerkID,
LocatieNetwerkPartner,
LocatieLon,
LocatieLat,
LocatieZoom
FROM
ZMLocatie
) d
WHERE
d.Dist < @Distance
ORDER BY d.Dist
END
else
Begin
select d.*
FROM (
SELECT
IsNull(dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon),0) AS Dist,
LocatieID,
LocatieNaam,
LocatieBezoekStraat,
LocatieBezoekNummer,
LocatieBezoekPlaatsnaam,
LocatieBezoekPostcode,
LocatieHoofdNetwerkID,
LocatieNetwerkPartner,
LocatieLon,
LocatieLat,
LocatieZoom
FROM
ZMLocatie
) d
WHERE
d.Dist < @Distance
AND
(LocatieHoofdNetwerkID = @NetwerkPZID)
or
EXISTS
(Select * from ZMproduct Where (ZMproduct.ProductLocatieID = LocatieID) AND Exists (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = ZMproduct.ProductID AND ZMNetwerkPZProduct.NetwerkPZID = @NetwerkPZID))
ORDER BY d.Dist
END
[/code]

Chirag

http://chirikworld.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 06:17:40

Yes, IsNull would work to, thanks...
but still the same problem.

I think it might be the precision of the float.
How can I define it in the sp.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 06:28:49

what are the parameters you are passing to your sp?

Chirag

http://chirikworld.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 06:31:41

@LATI = 52.378669,
@LNG = 4.884643,
@Distance = 5,
@NetwerkPZID = 3

I have this other sp which returns 0 for the distance, and values like the above lat and lon (so with 6 values behind the . ) which works. So I asume if the sp returns 6 values behind the , it will work.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 06:39:44

By the way CalculateDistance function looks like this.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[CalculateDistance]
(
@LATI float,
@LNG float,
@DBLAT float,
@DBLONG float
)

RETURNS float

AS
BEGIN
RETURN 6371.04 * acos(cos(pi() / 2 - radians(90.0 - @DBLAT)) *
COS(PI() / 2 - RADIANS(90.0 - @LATI)) * COS(RADIANS(@DBLONG) -
RADIANS(@LNG)) + SIN(PI() / 2 - RADIANS(90.0 - @DBLAT)) * SIN(PI() / 2
- RADIANS(90.0 - @LATI)))
END

I figure that if this returns a precision of six values behind the comma, it might work, so something like 0,210859 instead of 0,210859566504426

I tried to do RETURNS float(2,6) but it wouldn't work

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 06:55:31

How about returing Numeric(6,6)??

Chirag

http://chirikworld.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 06:58:03

This gives an error, I guess some conversion needs to take place somewhere.

Msg 8115, Level 16, State 8, Procedure ZMGetPointsZoekenOpStraal, Line 41
Arithmetic overflow error converting int to data type numeric.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 06:58:54

line 41 is: ORDER BY d.Dist

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 07:00:42

quote:
Originally posted by trouble2

This gives an error, I guess some conversion needs to take place somewhere.

Msg 8115, Level 16, State 8, Procedure ZMGetPointsZoekenOpStraal, Line 41
Arithmetic overflow error converting int to data type numeric.

What you tried??

Chirag

http://chirikworld.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 07:07:19

I tried the numeric(6,6)

I guess it works, but when I run

DECLARE @return_value int

EXEC @return_value = [dbo].[ZMGetPointsZoekenOpStraal]
@LATI = 52.378669,
@LNG = 4.884643,
@Distance = 5,
@NetwerkPZID = 3

SELECT 'Return Value' = @return_value

It gives the error in the order by statement.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 07:34:45

quote:
Originally posted by trouble2

I tried the numeric(6,6)

I guess it works, but when I run

DECLARE @return_value int

EXEC @return_value = [dbo].[ZMGetPointsZoekenOpStraal]
@LATI = 52.378669,
@LNG = 4.884643,
@Distance = 5,
@NetwerkPZID = 3

SELECT 'Return Value' = @return_value

It gives the error in the order by statement.

I dont think u will get anything in the return values, since there is no out parameter set..

Chirag

http://chirikworld.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 07:37:45

I have some clue,

I had:
IsNull(dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon), 1000) AS Dist,

which gave me the error:

Arithmetic overflow error converting int to data type numeric.

If I change to:

IsNull(dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon), 1000.000000) AS Dist,

It gives:

Arithmetic overflow error converting numeric to data type numeric.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 07:45:46

Changed numeric(6,6) to numeric(10,6)

Seems to do something:

1234,123456 : length = 10, numbers behind . = 6 (10,6)

The only thing is...

It still didn't solve my problem.
SQL gives me 8 results including two with dist = (1000.000000)
In the browser I still only get the two results with dis = 100.000000,
I would ofcourse like the other ones....

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 07:53:05

Hey just a sec, are you trying to avoid all values which return null from that function??

Means from that function, the values which returns null you want to avoid in your selections?

Chirag

http://chirikworld.blogspot.com/

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-11-14 : 08:25:19

Nevermind, I solved it, it was some other issue.

Thanks for all the help anyway.
The stored procedure has been improved by all this anyway....

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 09:27:47

Little bit modifiction to your SP

See if this work for you as expcted..



ALTER PROCEDURE [dbo].[ZMGetPointsZoekenOpStraal]
(
@LATI float,
@LNG float,
@Distance int,
@NetwerkPZID int = -1)

AS
if @netwerkPZID = -1
BEGIN
select d.*
FROM 
(
	SELECT
	dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS Dist,
	LocatieID,
	LocatieNaam,
	LocatieBezoekStraat,
	LocatieBezoekNummer,
	LocatieBezoekPlaatsnaam,
	LocatieBezoekPostcode,
	LocatieHoofdNetwerkID,
	LocatieNetwerkPartner,
	LocatieLon,
	LocatieLat,
	LocatieZoom
	FROM ZMLocatie
 ) d
WHERE
d.Dist < @Distance
AND
(
	LocatieHoofdNetwerkID = @NetwerkPZID Or @NetwerkPZID = -1
)
or
EXISTS (
	Select * from ZMproduct Where (ZMproduct.ProductLocatieID = LocatieID And @NetwerkPZID <> -1
       )
AND 
Exists (
		
	select * from ZMNetwerkPZProduct Where (ZMNetwerkPZProduct.ProductID = ZMproduct.ProductID AND 
	ZMNetwerkPZProduct.NetwerkPZID = @NetwerkPZID And @NetwerkPZID <> -1 )
	)
ORDER BY d.Dist


END

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources