Must declare the scalar variable

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

General SQL Server Forums

New to SQL Server Programming

Must declare the scalar variable

Author

Topic

trevorjdaniel
Starting Member

5 Posts

Posted - 2009-09-30 : 08:03:47

Hi,

Can anyone help please?

I have found a very useful article on how to do a find nearby lookup using SQL Server but I am getting a strange error when I use the code.

The example is on this page:

http://timheuer.com/blog/archive/2007/01/31/13975.aspx

I have used the scripts at the bottom of the page to create all the necessary db objects with no errors.

I then added a row to the locations table with the follwing dummy data

LocationName: test
Address: test
City: test
State: te
Zip: test
Latitude: 51.48842
Longitude: -3.17747
XAxis: 0
YAxis: 0
ZAxis: 0

I then run the sql "UPDATE Locations SET XAxis = dbo.XAxis(@lat, @long)" and I get the following error:

"Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@lat"

Can anyone tell me whats going wrong please?

Many thanks

Trev

www.taxiroute.co.uk - get a fare price!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 08:09:40

You need to declare and set the values for those variables

Madhivanan

Failing to plan is Planning to fail

trevorjdaniel
Starting Member

5 Posts

Posted - 2009-09-30 : 08:15:59

Thanks for the reply...

Where do I do that please?

Trev

www.taxiroute.co.uk - get a fare price!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 08:22:19

quote:
Originally posted by trevorjdaniel

Thanks for the reply...

Where do I do that please?

Trev

www.taxiroute.co.uk - get a fare price!

declare @lat decimal(18,6), @long decimal(18,6)
select @lat=51.48842, @long=-3.17747

UPDATE Locations SET XAxis = dbo.XAxis(@lat, @long)

Madhivanan

Failing to plan is Planning to fail

trevorjdaniel
Starting Member

5 Posts

Posted - 2009-09-30 : 08:45:18

so i need to amend the function?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[XAxis]
(
@lat float,
@lon float
)
RETURNS float
AS
BEGIN
RETURN cos(radians(@lat)) * cos(radians(@lon))
END

sorry to be a pain - where do i change it?

Thanks

Trev

www.taxiroute.co.uk - get a fare price!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 08:47:57

quote:
Originally posted by trevorjdaniel

so i need to amend the function?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[XAxis]
(
@lat float,
@lon float
)
RETURNS float
AS
BEGIN
RETURN cos(radians(@lat)) * cos(radians(@lon))
END

sorry to be a pain - where do i change it?

Thanks

Trev

www.taxiroute.co.uk - get a fare price!

First create the function and then try what I suggested

Madhivanan

Failing to plan is Planning to fail

trevorjdaniel
Starting Member

5 Posts

Posted - 2009-09-30 : 09:39:27

Hi Madhivanan,

I must admit, I am confused. I am sure you know what you are talking about and I am sure i don't ! :)

This is what I thought I had to do...

Add rows to the database table with correct lat/long but with zeros in the xaxis, yaxis and zaxis...

then i though i just needed to run the three functions xaxis, yaxis and zaxis and that would update the zeros to the correct values for all the rows in the table.

have i misunderstood what these functions do and how to use them?

I really appreciate your help because im lost at the moment

trev

www.taxiroute.co.uk - get a fare price!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 10:34:03

quote:
Originally posted by trevorjdaniel

Hi Madhivanan,

I must admit, I am confused. I am sure you know what you are talking about and I am sure i don't ! :)

This is what I thought I had to do...

Add rows to the database table with correct lat/long but with zeros in the xaxis, yaxis and zaxis...

then i though i just needed to run the three functions xaxis, yaxis and zaxis and that would update the zeros to the correct values for all the rows in the table.

have i misunderstood what these functions do and how to use them?

I really appreciate your help because im lost at the moment

trev

www.taxiroute.co.uk - get a fare price!

Yes try what you said

Madhivanan

Failing to plan is Planning to fail

trevorjdaniel
Starting Member

5 Posts

Posted - 2009-09-30 : 10:55:28

ok, ive got it sussed. thanks for your help...

trev

www.taxiroute.co.uk - get a fare price!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources