Return a result set from a table AND a stored procedure together? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Return a result set from a table AND a stored procedure together?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-25 : 07:40:30

Richard writes "I have store proc that calculates distance in miles between 2 sets of latitudes and longitudes and I also have a table called [places] that contain zipcodes, lats, & longs. I need to combine the two in some way so I can pass 2 parameters such as zipcode and radius (let's say 11214, 3) and return a record set of all zipcodes within that given mile radius (range.) The output should be something like the following if I pass it 11214, 3:


11214    1
11228    3
11219    2

etc.... Please help!? I am running MS-SQL2000 and will be calling this code from ASP.

Thanks!

Richard"

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-25 : 08:26:26

It's easier to show the modifications if you post some of your code.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 08:40:24

just edit your stored proc (or create a new copy) to include a join to this table. but, as Sam says, some code would be helpful.

- Jeff

anteros511
Starting Member

3 Posts

Posted - 2003-11-26 : 15:25:48

--**************************************
-- Name: Procedure to calc miles distance from two points on earth
-- Description:This procedure is used to calc miles distance from
-- two points on earth. You can get latitude and longitude for places on
-- earth, then just use this procedure to calc how many miles are in between.
--
-- This code is copyrighted and has limited warranties.
-- Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.644/lngWId.5/qx/vb/scripts/ShowCode.htm
-- for details.
--**************************************

CREATE Proc dbo.sp_MilesLatLong (
@P_Lat1 decimal(8,4) =0 ,
@P_Long1 decimal(8,4) =0 ,
@P_Lat2 decimal(8,4) =0 ,
@P_Long2 decimal(8,4) =0 ,
@P_DiffMiles decimal(10,2) =0 output
)
As
Begin
/*************************************************************/
/* Routine to calc miles distance from two points on earth */
/* specified in latitude/longitude pairs.*/
/* Inputs are "degrees.minutes" ie: 33.0654 */
/*************************************************************/
set nocount on
declare @Lat1 decimal(28,10)
declare @Long1 decimal(28,10)
declare @Lat2 decimal(28,10)
declare @Long2 decimal(28,10)
declare @d decimal(28,10)

/* Default return value */
set @P_DiffMiles = 0

/* Convert to radians */
set @Lat1 = @P_Lat1 / 57.2958
set @Long1 = @P_Long1 / 57.2958
set @Lat2 = @P_Lat2 / 57.2958
set @Long2 = @P_Long2 / 57.2958

/* Calc distance */
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))

/* Convert to miles */
if @d <> 0
begin
set @P_DiffMiles = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
end
GO

/* DECLARE @miles int
/* EXEC @miles = sp_MilesLatLong 41.598994, -73.996119, 41.768420, -73.960450
/* SELECT miles = @miles

anteros511
Starting Member

3 Posts

Posted - 2003-11-26 : 15:26:46

Sample data from my 'Places' table:

AL 35085 32.965120 -86.744053
AL 35087 34.303718 -86.583234
AL 35089 32.941708 -86.060988
AL 35091 33.771090 -86.806727
AL 35094 33.530698 -86.555065
AL 35096 33.605233 -86.120796
AL 35097 33.896526 -86.630569
AL 35098 34.120006 -87.050758
AL 350HH 33.464476 -86.577571
AL 350XX 32.978292 -86.499261
AL 35111 33.279946 -87.101488
AL 35112 33.681899 -86.482375
AL 35114 33.225377 -86.864434

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-26 : 19:51:48

[code]
CREATE TABLE places
(
statecode char(2),
zipcode char(5),
lat float,
long float
)

insert into places select 'AL', '35085' ,32.965120 ,-86.744053
union all select 'AL', '35087', 34.303718 ,-86.583234
union all select 'AL', '35089', 32.941708 ,-86.060988
union all select 'AL', '35091', 33.771090 ,-86.806727
union all select 'AL', '35094', 33.530698 ,-86.555065
union all select 'AL', '35096', 33.605233 ,-86.120796
union all select 'AL', '35097', 33.896526 ,-86.630569
union all select 'AL', '35098', 34.120006 ,-87.050758
union all select 'AL', '350HH', 33.464476 ,-86.577571
union all select 'AL', '350XX', 32.978292 ,-86.499261
union all select 'AL', '35111', 33.279946 ,-87.101488
union all select 'AL', '35112', 33.681899 ,-86.482375
union all select 'AL', '35114', 33.225377 ,-86.864434
GO

CREATE PROC up_FindZipCodesWithinRadius

@ZipCode char(5) ,
@GivenMileRadius int
AS
SET NOCOUNT ON

DECLARE @lat1 float,
@long1 float

SELECT @lat1= lat,
@long1 = long
FROM places
WHERE zipcode = @ZipCode

SELECT ZipCode ,MileRadius
FROM
(
SELECT ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))), 2)) /
((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))))) MileRadius
FROM Places
) a
WHERE a.MileRadius <= @GivenMileRadius
AND ZipCode <> @ZipCode
ORDER BY MileRadius

GO

EXEC up_FindZipCodesWithinRadius '35085',20
GO
DROP PROC up_FindZipCodesWithinRadius
--DROP TABLE places
[/code]

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-26 : 20:06:28

quote:
This code is copyrighted and has limited warranties.

Oh please!!! Copywriting Maths now are we.. F$^%&ing morons....

Look out ehorn, Pythagoras is going to start suing!!!!

DavidM

"SQL-3 is an abomination.."

anteros511
Starting Member

3 Posts

Posted - 2003-11-28 : 23:05:02

DUDE!! You rock-- thank you sssooo much!!!

quote:

Originally posted by ehorn


CREATE PROC up_FindZipCodesWithinRadius

	@ZipCode char(5) ,
	@GivenMileRadius int
...

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-11-29 : 03:35:11

hi anteros511,

Dont give prefix as "sp_", when create stored procedure.Its not adviseable.

":-) IT Knowledge is power :-)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources