Must pass parameter as @name = value

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)

Must pass parameter as @name = value

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-09-29 : 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

30421 Posts

Posted - 2006-09-29 : 08:38:39

ORDER BY 1 ~~dist~~ DESC

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-29 : 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

267 Posts

Posted - 2006-09-29 : 08:47:13

ORDER BY 1 DESC

Does not seem to matter, any other ideas?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-09-29 : 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

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-09-29 : 08:57:39

Also tried derived table, does not seem to work....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-29 : 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

267 Posts

Posted - 2006-09-29 : 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

30421 Posts

Posted - 2006-09-29 : 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

267 Posts

Posted - 2006-09-29 : 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

30421 Posts

Posted - 2006-09-29 : 09:24:49

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-29 : 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

267 Posts

Posted - 2006-09-29 : 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

267 Posts

Posted - 2006-09-29 : 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,

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2006-10-02 : 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

22859 Posts

Posted - 2006-10-02 : 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

30421 Posts

Posted - 2006-10-02 : 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

22864 Posts

Posted - 2006-10-02 : 09:48:47

>>When did that change?

Without any notice

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 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

267 Posts

Posted - 2006-10-02 : 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

22864 Posts

Posted - 2006-10-02 : 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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources