how to pass parameter to functions

Question

I am having a Zip table that has zip code, longitude and latitude, city, state .. all in nvarchar(50). And I downloaded these functions below. Now I am asking how do I pass the parameters to those functions given my Zip table's data types?

ALTER FUNCTION [dbo].[RadiusFunc]

(
	@ZipCode nvarchar(5),
	@Miles decimal(18, 9)
)

RETURNS
	@MaxLongLats TABLE
	(
		Latitude decimal(10,8),
		Longitude decimal(11,8),
		MaxLatitude decimal(10,8),
		MinLatitude decimal(10,8),
		MaxLongitude decimal(11,8),
		MinLongitude decimal(11,8)
	)
AS

ALTER FUNCTION [dbo].[DistanceFunc]
(
	@Latitude1 decimal(11,6),
	@Longitude1 decimal(11,6),
	@Latitude2 decimal(11,6),
	@Longitude2 decimal(11,6)
)

Answer 1

This function is missing the second part. What do you mean by this inside another function? What are you trying to do?

ALTER FUNCTION [dbo].[DistanceFunc]
(
	@Latitude1 decimal(11,6),
	@Longitude1 decimal(11,6),
	@Latitude2 decimal(11,6),
	@Longitude2 decimal(11,6)
)

---

Abdallah, PMP, ITIL, MCTS

Answer 2

I am having a Zip table that has zip code, longitude and latitude, city, state .. all in nvarchar(50). And I downloaded these functions below. Now I am asking how do I pass the parameters to those functions given my Zip table's data types?

You can use the CAST or CONVERT functions to resolve the data type issue.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 3

This function is missing the second part. What do you mean by this inside another function? What are you trying to do?

ALTER FUNCTION [dbo].[DistanceFunc]
(
	@Latitude1 decimal(11,6),
	@Longitude1 decimal(11,6),
	@Latitude2 decimal(11,6),
	@Longitude2 decimal(11,6)
)

---

Abdallah, PMP, ITIL, MCTS

No, sorry this is another function. 

I am calling it in a stored procedure like this:

SELECT ZIP.ZipCode, ZIP.City, 
	dbo.DistanceFunc(ZIP.Latitude, ZIP.Longitude, RAD.Latitude, RAD.Longitude) As Distance
	INTO #TempZips
FROM ZipCodes ZIP, RadiusFunc('"83706"', 15.0) RAD
WHERE (ZIP.Latitude BETWEEN RAD.MinLatitude AND RAD.MaxLatitude) AND
	(ZIP.Longitude BETWEEN RAD.MinLongitude AND RAD.MaxLongitude) AND
	(dbo.DistanceFunc(ZIP.Latitude,ZIP.Longitude,RAD.Latitude,RAD.Longitude) <= 15.0)

But it's not working. Am I missing something?

Answer 4

It doesn't give me any conversion errors, but I am just not sure how to call it correctly.

Answer 5

Check the APPLY operator in BOL.

> FROM ZipCodes ZIP, RadiusFunc('"83706"', 15.0) RAD

...
FROM ZipCodes ZIP CROSS APPLY RadiusFunc(ZIP.ZIPCODE, 15.0) RAD
...


AMB

Answer 6

Check the APPLY operator in BOL.

> FROM ZipCodes ZIP, RadiusFunc('"83706"', 15.0) RAD

...
FROM ZipCodes ZIP CROSS APPLY RadiusFunc(ZIP.ZIPCODE, 15.0) RAD
...


AMB

Shouldn't I put the Zip.ZipCode as 83706 in that CROSS APPLY?

Answer 7

Check the APPLY operator in BOL.

> FROM ZipCodes ZIP, RadiusFunc('"83706"', 15.0) RAD

...
FROM ZipCodes ZIP CROSS APPLY RadiusFunc(ZIP.ZIPCODE, 15.0) RAD
...


AMB

Shouldn't I put the Zip.ZipCode as 83706 in that CROSS APPLY?

You can use the "where" clause if you are just interested in a group of rows from your zip codes table


...
FROM ZipCodes ZIP CROSS APPLY RadiusFunc(ZIP.ZIPCODE, 15.0) RAD
where zip.zipcode = "83706"
...


AMB

Answer 8

Check the APPLY operator in BOL.

> FROM ZipCodes ZIP, RadiusFunc('"83706"', 15.0) RAD

...
FROM ZipCodes ZIP CROSS APPLY RadiusFunc(ZIP.ZIPCODE, 15.0) RAD
...


AMB

Shouldn't I put the Zip.ZipCode as 83706 in that CROSS APPLY?

You can use the "where" clause if you are just interested in a group of rows from your zip codes table


...
FROM ZipCodes ZIP CROSS APPLY RadiusFunc(ZIP.ZIPCODE, 15.0) RAD
where zip.zipcode = "83706"
...


AMB

Hunchback, that doesn't work either. It gives nothing. When I do SELECT * FROM ZipCodes WHErE Zipcode = '83706', it gives me Boise.

Answer 9

SELECT *
FROM ZipCodes as Z cross apply RadiusFunc(Z.Zipcode , 15.0) as R
WHERE Z.Zipcode = '83706';


AMB

Answer 10

At least this SELECT * 
FROM ZipCodes as Z cross apply RadiusFunc(Z.Zipcode , 15.0) as R
WHERE Z.Zipcode = '83706'; gives me the whole table, but still doesn't display the result as Boise. Something wrong with the RadiusFunc?

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[RadiusFunc]

(
	@ZipCode nvarchar(5),
	@Miles decimal(18, 9)
)

RETURNS
	@MaxLongLats TABLE
	(
		Latitude decimal(10,8),
		Longitude decimal(11,8),
		MaxLatitude decimal(10,8),
		MinLatitude decimal(10,8),
		MaxLongitude decimal(11,8),
		MinLongitude decimal(11,8)
	)
AS

BEGIN
	--Declare variables
	DECLARE @Latitude decimal(10,8), @Longitude decimal(11,8)
	DECLARE @MaxLatitude decimal(10, 8), @MinLatitude decimal(10, 8)
	DECLARE @MaxLongitude decimal(11, 8), @MinLongitude decimal(11, 8)

	--Get the Lat/Long of the zipcode
	SELECT @Latitude = Latitude, @Longitude = Longitude
	FROM dbo.ZipCodes
	WHERE ZipCode = @ZipCode

	--Zipcode not found?
	IF @@ROWCOUNT = 0
		RETURN 

	--Determine the maxes (69.17 is the # of miles/degree)
	SET @MaxLatitude = @Latitude + @Miles / 69.17
	SET @MinLatitude = @Latitude - (@MaxLatitude - @Latitude)
	SET @MaxLongitude = @Longitude + @Miles / (COS(@MinLatitude * PI() / 180) * 69.17)
	SET @MinLongitude = @Longitude - (@MaxLongitude - @Longitude)

	--Insert data into return table
	INSERT INTO @MaxLongLats
		(Latitude, Longitude, MaxLatitude, MinLatitude, MaxLongitude, MinLongitude)
	SELECT @Latitude, @Longitude, @MaxLatitude, @MinLatitude, @MaxLongitude, @MinLongitude
	RETURN
END

Answer 11

It comes down to my latitude and logitude fields that are saved as Nvarchar(50). I cannot conver/cast those fields to numerical data type. anyone has tricks?

Answer 12

Can't you use CAST or CONVERT as SQLUSA suggested?

Or is your latitude and longitude in some non-numeric format (like "37° 37' N" and "122° 23' W")?

---

--Brad (My Blog)

Answer 13

Can't you use CAST or CONVERT as SQLUSA suggested?

Or is your latitude and longitude in some non-numeric format (like "37° 37' N" and "122° 23' W")?

---

--Brad (My Blog)

I don't understand why I cannot conver/cast it. I am trying to convert it to float, numeric, and it keeps saying Error converting from nvarchar to whatever data type I was trying to convert to.

The values are Numbers. Latitude has "-" minus in front of it. NOt sure if that matters, but longitude are positive numbers.

Answer 14

Try this...

SELECT *
FROM ZipCodes
WHERE ISNUMERIC(Latitude)=0 OR ISNUMERIC(Longitude)=0

That will at least tell you which values are not numeric (and therefore can't be CAST or CONVERTed correctly).

---

--Brad (My Blog)

Answer 15

Oh Brad, you are the man ;) hehe.... thank you. there's a date that didn't get imported correctly. It puts in the column name as fields, zip, longitude, etc.

Answer 16

Yup, that's the issue due to the data type conversion. Now it gives me every city within the given distance ;) Thanks to all.