| Author |
 Topic  |
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
 Posted - 09/29/2006 : 08:33:10
|
Hi there, I'm trying to use a SP and a function which I found somewhere, only I get an error.
The function looks like this:
CREATE 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
And the sp looks like this:
CREATE PROCEDURE [dbo].[FindNearest]
(
@LATI float,
@LNG float,
@Distance float
)
AS
SELECT
dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS dist,
LocatieID,
LocatieNaam,
LocatieBezoekStraat,
LocatieBezoekNummer,
LocatieBezoekPostcode,
LocatieBezoekPlaatsnaam,
LocatieHoofdNetwerkID,
LocatieNetwerkPartner,
LocatieLon,
LocatieLat,
LocatieZoom
FROM
ZMLocatie
WHERE
dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) < @Distance
ORDER BY dist
When I try to run it, SQL comes up with:
Msg 119, Level 15, State 1, Line 4
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Can anyone explain what this means, and what I should do about it? |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/29/2006 : 08:38:39
|
ORDER BY 1 dist DESC
Peter Larsson
Helsingborg, Sweden |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 09/29/2006 : 08:44:02
|
or you need to use it as Derived table
Select * from
(your query)
T order by DIST
Can you show us how you executed that procedure?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 09/29/2006 : 08:47:13
|
ORDER BY 1 DESC
Does not seem to matter, any other ideas? |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 09/29/2006 : 08:48:24
|
It said:
USE [npz]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[FindNearest]
@LATI = 52,352723,
@LNG = 4,863569,
@Distance = 2
SELECT 'Return Value' = @return_value
GO |
Edited by - trouble2 on 09/29/2006 08:50:33 |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 09/29/2006 : 08:57:39
|
| Also tried derived table, does not seem to work.... |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 09/29/2006 : 08:58:45
|
You should use Output parameter to get value into a Variable
Read about it sql server help file
Madhivanan
Failing to plan is Planning to fail |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 09/29/2006 : 09:01:24
|
I think I understand what you mean, but how and where do you do that.
declare @somevariable
and then... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/29/2006 : 09:02:10
|
quote:
Originally posted by trouble2
ORDER BY 1 DESC
Does not seem to matter, any other ideas?
Did you even try to test?
You can't ORDER BY aliases, as you can do in ACCESS.
If you don't understand ORDER BY 1 DESC, just tell us, or try
ORDER BY dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) DESC, which is the same thing.
Peter Larsson
Helsingborg, Sweden |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 09/29/2006 : 09:08:45
|
Don't get angry, I just didn't know...
Indeed I thought it was some kind of alias, and I did try.
Like I said... it didn't work.
It seems I've got to use output parameters... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/29/2006 : 09:24:49
|
Either of these two queries work great for meSELECT dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS dist,
LocatieID,
LocatieNaam,
LocatieBezoekStraat,
LocatieBezoekNummer,
LocatieBezoekPostcode,
LocatieBezoekPlaatsnaam,
LocatieHoofdNetwerkID,
LocatieNetwerkPartner,
LocatieLon,
LocatieLat,
LocatieZoom
FROM ZMLocatie
WHERE dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) < @Distance
ORDER BY 1 And thisselect d.*
FROM (
SELECT dbo.CalculateDistance(@LATI, @LNG, LocatieLat, LocatieLon) AS Dist,
LocatieID,
LocatieNaam,
LocatieBezoekStraat,
LocatieBezoekNummer,
LocatieBezoekPostcode,
LocatieBezoekPlaatsnaam,
LocatieHoofdNetwerkID,
LocatieNetwerkPartner,
LocatieLon,
LocatieLat,
LocatieZoom
FROM ZMLocatie
) d
WHERE d.Dist < @Distance
ORDER BY d.Dist
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 09/29/2006 09:25:34 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 09/29/2006 : 09:29:00
|
Peso, the problem is that the questioner is not using Output parameter 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 09/29/2006 : 09:38:12
|
Yes, well now that we know, could you please, please, please help me with that...
I don't understand what I am doing wrong.
|
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 09/29/2006 : 11:33:53
|
It had nothing to do with output parameters... check my values:
@LATI = 52,352723,
@LNG = 4,863569,
I should have done:
@LATI = 52.352723,
@LNG = 4.863569,
 |
Edited by - trouble2 on 10/02/2006 03:28:54 |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 10/02/2006 : 03:29:55
|
quote:
Originally posted by trouble2
It had nothing to do with output parameters... check my values:
@LATI = 52,352723,
@LNG = 4,863569,
I should have done:
@LATI = 52.352723,
@LNG = 4.863569,
Failing to look is uuuhm.... well what is it?!?!??!??! |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/02/2006 : 08:41:54
|
"When I try to run it, SQL comes up with:
Msg 119, Level 15, State 1, Line 4
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'."
I don't see this answered, in case its still an issue then in general you can do:
EXEC dbo.FindNearest @LATI=1.234, @LNG=4.5678, @Distance=6.7890
and
EXEC dbo.FindNearest 1.234, 4.5678, 6.7890
but that's not recommended in case the sequence of declared parameters changes in the future
and you can even do
EXEC dbo.FindNearest 1.234, 4.5678, @Distance=6.7890
which is handy if you want to leave out, say, the 3rd, 4th ... parameters [assuming they are optional] and just specify the Nth parameters. Again, not recommended for the first 2 parameters to not have their names.
But you cannot do
EXEC dbo.FindNearest @LATI=1.234, @LNG=4.5678, 6.7890
i.e. name some parameters and NOT all the REMAINING parameters, so this would also be wrong:
EXEC dbo.FindNearest 1.234, @LNG=4.5678, 6.7890
but only with regard to the last parameter.
I always name the parameters so I'm a bit hazy on the exact details of this, but I think there are some circumstances where you can get away with this which may relate to compulsory, rather than optional, Sproc parameters being unnamed, so you may be able to get away with "looser" code than I'm suggesting, but either way I think that not naming the parameters to an SProc call is bad practice.
Unfortunately you cannot name parameters in a Function call, so that rather weakens the deal - you could easily pass Lat/Long/DBlat and Distance in the wrong order - all the figures look pretty similar, after all!
"You can't ORDER BY aliases, as you can do in ACCESS"
You sure Peso?
SELECT [Dist] = D,
[Lat] = L
FROM
(
SELECT [D] = 1,
[L] = 200
UNION ALL
SELECT [D] = 2,
[L] = 100
) AS X
ORDER BY [dist]
or am I misunderstanding something?
Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 10/02/2006 : 09:01:53
|
No, you're right. My bad.
When did that change? I have avoided using ORDER BY aliases since from a long time ago when it didn't work. I assumed it was still so.
Hey, I learned something new today!
Peter Larsson
Helsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 10/02/2006 : 09:48:47
|
>>When did that change?
Without any notice
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/02/2006 : 10:09:53
|
"I have avoided using ORDER BY aliases since from a long time ago when it didn't work"
I think the practice (old and new way) is a bit dubious:
SELECT [MyDate] = CONVERT(varchar(11), MyDate, 11)
FROM MyTable
ORDER BY MyDate
is now ordering alphabetically, rather than chronologically!
Bug, Feature, Feature-introduced-by-Bug-fix or Bug-introduced-by-Feature!
Kristen |
|
|
|
trouble2
Constraint Violating Yak Guru
Netherlands
266 Posts |
Posted - 10/02/2006 : 10:25:35
|
Thanks anyway....
It's working great. I've found it on the google maps api blog.
Works really nicely with the google maps api to calculate points which fall within some radius (in km) of another point.
So this way I can make people enter their zip code and find some locations within 5 - 10 - 15 km's. Very cool. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 10/02/2006 : 10:26:30
|
Kristen, How about these?
Select ISNUMERIC(12e3)
Select ISDATE(2005)
Select cast('2006' as Datetime)
Select cast(2006 as Datetime)
Madhivanan
Failing to plan is Planning to fail |
Edited by - madhivanan on 10/02/2006 10:28:41 |
|
|
|
Topic |
|
|
|
Must pass parameter as @name = value
