Returning values from a stored procedure - Help needed!

Hello everyone,
I'm using the following procedure to return the result of a zip code
radius search at my website. As it is, the proc is working fine except
I'm having a problem returning a value for the distance between 2
points. The procedure calculates the distance between the 2 points and
compares it in the following select statement
SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
BROKEN DOWN
****@.distance is the distance inputed by a user (form post - value up
to 20 miles) and this formula calculates the distance between 2
points*****
>3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.
3) * COS((Longitude/57.3) - (@.lng/57.3)))
****Now what I need to know is how can i return this distance value
through the procedure.
(3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) *
COS(Latitude/57.3) * COS((Longitude/57.3) - (@.lng/57.3))))
---
Following is the complete proc
---
CREATE PROCEDURE dbo.psFindSitters1
(@.zipcode char(7),
@.distance int
)
AS
SET NOCOUNT ON
Declare @.Result varchar(90)
Declare @.lat decimal(9,6)
Declare @.lng decimal(9,6)
Declare @.lat1 decimal(9,6)
Declare @.lng1 decimal(9,6)
Declare @.Latitude decimal(9,6)
Declare @.Longitude decimal(9,6)
Declare @.count int
Declare @.distance1 int
Declare @.distance2 int
set @.Result = 'err';
--find the parent
if (@.zipcode<>0) begin --find LAT and LNG
select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
@.zipcode
end;
if (@.lat<> 0) begin --another query
SELECT @.count=count(*) FROM tbSitters WHERE bsZipCode in (SELECT
ZipCode FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
if (@.count > 0) begin --another query
set @.Result = 'user';
SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
set @.Result = 'nouser';
select @.Result as [Result];
--GoTo hasErr;
end;
GO
Thanks in advance for any help!
RobertIf you want the distance to sitter returned in the (table) result set, join
tbSitters to tbZip
(*untested*)
if (@.count > 0) begin --another query
set @.Result = 'user';
SELECT @.Result as [Result], s.bsID, s.bsFirstName, s.bsAge, s.bsIntsection,
s.bsStartWage, s.bsStatus,
(3959 * ACOS(SIN(@.lat/57.3) * SIN(z.Latitude/57.3) + COS(@.lat/57.3) *
COS(z.Latitude/57.3) * COS((z.Longitude/57.3) - (@.lng/57.3)))) AS "Distance"
FROM tbSitters s INNER JOIN tbZip z ON s.bsZipCode = z.ZipCode
WHERE s.bsZipCode in (SELECT ZipCode
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
end;
"ROBinBRAMPTON" wrote:

> Hello everyone,
> I'm using the following procedure to return the result of a zip code
> radius search at my website. As it is, the proc is working fine except
> I'm having a problem returning a value for the distance between 2
> points. The procedure calculates the distance between the 2 points and
> compares it in the following select statement
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> BROKEN DOWN
> ****@.distance is the distance inputed by a user (form post - value up
> to 20 miles) and this formula calculates the distance between 2
> points*****
> ****Now what I need to know is how can i return this distance value
> through the procedure.
> (3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) *
> COS(Latitude/57.3) * COS((Longitude/57.3) - (@.lng/57.3))))
> ---
> Following is the complete proc
> ---
> CREATE PROCEDURE dbo.psFindSitters1
> (@.zipcode char(7),
> @.distance int
> )
> AS
> SET NOCOUNT ON
> Declare @.Result varchar(90)
> Declare @.lat decimal(9,6)
> Declare @.lng decimal(9,6)
> Declare @.lat1 decimal(9,6)
> Declare @.lng1 decimal(9,6)
> Declare @.Latitude decimal(9,6)
> Declare @.Longitude decimal(9,6)
> Declare @.count int
> Declare @.distance1 int
> Declare @.distance2 int
> set @.Result = 'err';
> --find the parent
> if (@.zipcode<>0) begin --find LAT and LNG
> select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
> @.zipcode
> end;
> if (@.lat<> 0) begin --another query
> SELECT @.count=count(*) FROM tbSitters WHERE bsZipCode in (SELECT
> ZipCode FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
>
> if (@.count > 0) begin --another query
> set @.Result = 'user';
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
> set @.Result = 'nouser';
> select @.Result as [Result];
> --GoTo hasErr;
> end;
>
> GO
> Thanks in advance for any help!
> Robert
>|||It worked perfectly!
Thanks very much Mark
Robert
p.s. If you're a parent in need of a sitter? Open an account then drop
me an email at admin at phone a babysitter dot com, and I'll upgrade it
for you as way way to express my gratitude.|||Also consider a re-write of the procedure using a temporary table
CREATE PROCEDURE dbo.psFindSitters1
(@.zipcode char(7),
@.distance int
)
AS
SET NOCOUNT ON
Declare @.Result varchar(90)
Declare @.lat decimal(9,6)
Declare @.lng decimal(9,6)
Declare @.lat1 decimal(9,6)
Declare @.lng1 decimal(9,6)
Declare @.Latitude decimal(9,6)
Declare @.Longitude decimal(9,6)
Declare @.count int
Declare @.distance1 int
Declare @.distance2 int
set @.Result = 'err';
--find the parent
if (@.zipcode<>0) begin --find LAT and LNG
select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
@.zipcode
end;
if (@.lat<> 0) begin --another query
SELECT ZipCode INTO #zipsinrange
FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
COS((Longitude/57.3) - (@.lng/57.3))) )
set @.count = @.@.ROWCOUNT
end;
if (@.count > 0) begin --another query
set @.Result = 'user';
SELECT @.Result as [Result], s.bsID, s.bsFirstName, s.bsAge, s.bsIntsection,
s.bsStartWage, s.bsStatus,
(3959 * ACOS(SIN(@.lat/57.3) * SIN(z.Latitude/57.3) + COS(@.lat/57.3) *
COS(z.Latitude/57.3) * COS((z.Longitude/57.3) - (@.lng/57.3)))) AS "Distance"
FROM tbSitters s INNER JOIN #zipsinrange r ON s.bsZipCode = r.ZipCode
INNER JOIN tblZip z ON z.ZipCode = r.ZipCode
end;
if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
set @.Result = 'nouser';
select @.Result as [Result];
--GoTo hasErr;
end;
GO
"ROBinBRAMPTON" wrote:

> Hello everyone,
> I'm using the following procedure to return the result of a zip code
> radius search at my website. As it is, the proc is working fine except
> I'm having a problem returning a value for the distance between 2
> points. The procedure calculates the distance between the 2 points and
> compares it in the following select statement
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> BROKEN DOWN
> ****@.distance is the distance inputed by a user (form post - value up
> to 20 miles) and this formula calculates the distance between 2
> points*****
> ****Now what I need to know is how can i return this distance value
> through the procedure.
> (3959 * ACOS(SIN(@.lat/57.3) * SIN(Latitude/57.3) + COS(@.lat/57.3) *
> COS(Latitude/57.3) * COS((Longitude/57.3) - (@.lng/57.3))))
> ---
> Following is the complete proc
> ---
> CREATE PROCEDURE dbo.psFindSitters1
> (@.zipcode char(7),
> @.distance int
> )
> AS
> SET NOCOUNT ON
> Declare @.Result varchar(90)
> Declare @.lat decimal(9,6)
> Declare @.lng decimal(9,6)
> Declare @.lat1 decimal(9,6)
> Declare @.lng1 decimal(9,6)
> Declare @.Latitude decimal(9,6)
> Declare @.Longitude decimal(9,6)
> Declare @.count int
> Declare @.distance1 int
> Declare @.distance2 int
> set @.Result = 'err';
> --find the parent
> if (@.zipcode<>0) begin --find LAT and LNG
> select @.lat=[Latitude],@.lng=[Longitude] FROM tbZip WHERE ZipCode =
> @.zipcode
> end;
> if (@.lat<> 0) begin --another query
> SELECT @.count=count(*) FROM tbSitters WHERE bsZipCode in (SELECT
> ZipCode FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
>
> if (@.count > 0) begin --another query
> set @.Result = 'user';
> SELECT @.Result as [Result], bsID, bsFirstName, bsAge, bsIntsection,
> bsStartWage, bsStatus FROM tbSitters WHERE bsZipCode in (SELECT ZipCode
> FROM tbZip WHERE @.distance > 3959 * ACOS(SIN(@.lat/57.3) *
> SIN(Latitude/57.3) + COS(@.lat/57.3) * COS(Latitude/57.3) *
> COS((Longitude/57.3) - (@.lng/57.3))) )
> end;
> if (@.lat is null or @.lat=0 or @.count is null or @.count=0) begin
> set @.Result = 'nouser';
> select @.Result as [Result];
> --GoTo hasErr;
> end;
>
> GO
> Thanks in advance for any help!
> Robert
>