SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Must pass parameter as @name = value
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  08:33:10  Show Profile  Reply with Quote
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
30249 Posts

Posted - 09/29/2006 :  08:38:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ORDER BY 1 dist DESC

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22756 Posts

Posted - 09/29/2006 :  08:44:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  08:47:13  Show Profile  Reply with Quote
ORDER BY 1 DESC

Does not seem to matter, any other ideas?
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  08:48:24  Show Profile  Reply with Quote
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
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  08:57:39  Show Profile  Reply with Quote
Also tried derived table, does not seem to work....
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22756 Posts

Posted - 09/29/2006 :  08:58:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  09:01:24  Show Profile  Reply with Quote
I think I understand what you mean, but how and where do you do that.

declare @somevariable

and then...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 09/29/2006 :  09:02:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  09:08:45  Show Profile  Reply with Quote
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...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 09/29/2006 :  09:24:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Either of these two queries work great for me
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	1
And this
select		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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22756 Posts

Posted - 09/29/2006 :  09:29:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Peso, the problem is that the questioner is not using Output parameter

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  09:38:12  Show Profile  Reply with Quote
Yes, well now that we know, could you please, please, please help me with that...

I don't understand what I am doing wrong.
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 09/29/2006 :  11:33:53  Show Profile  Reply with Quote
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
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 10/02/2006 :  03:29:55  Show Profile  Reply with Quote
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?!?!??!??!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/02/2006 :  08:41:54  Show Profile  Reply with Quote
"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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 10/02/2006 :  09:01:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22756 Posts

Posted - 10/02/2006 :  09:48:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>When did that change?

Without any notice

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/02/2006 :  10:09:53  Show Profile  Reply with Quote
"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
Go to Top of Page

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 10/02/2006 :  10:25:35  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22756 Posts

Posted - 10/02/2006 :  10:26:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000