September 26, 2004 at 12:48 pm
CREATE PROCEDURE zipcode_distance
@ZipStart nchar(5),
@ZipEnd nchar(5)
as
DECLARE @Lat1 REAL
DECLARE @Long1 REAL
DECLARE @Lat2 REAL
DECLARE @Long2 REAL
DECLARE @Result varchar(5)
SET @Result = 0
DECLARE @FirstExists BIT
SET @FirstExists = 1
SELECT @Lat1 = Latitude, @Long1 = Longitude FROM ZIPCodes WHERE ZIPCode = @ZIPStart AND CityType = 'D'
IF 0 = @@ROWCOUNT BEGIN
SET @FirstExists = 0
SET @Result = -1 /* ZIPStart not found */
END
SELECT @Lat2 = Latitude, @Long2 = Longitude FROM ZIPCodes WHERE ZIPCode = @ZIPEnd AND CityType = 'D'
IF 0 = @@ROWCOUNT BEGIN
IF 0 = @FirstExists SET @Result = -3 /* ZIPStart & ZIPEnd not found */
ELSE SET @Result = -2 /* ZIPEnd not found */
END
IF 0 = @Result SELECT dbo.DistanceAssistant(@Lat1,@Long1,@Lat2,@Long2) As Distance
ELSE SELECT @Result AS Distance
RETURN @Result
GO
I'm passing @ZipStart and @ZipEnd from form fields. "@Return" returns the values stated above (0, -2 or -3) (and I can return that to the ASP page with no problem), but what I really want to do is get the "Distance" value into something that I can pull back to the ASP page. My SQL experience to date is basically just insert and update, so I'm just kind of fumbling around and would appreciate the help.
September 26, 2004 at 1:07 pm
Hi,
A couple of changes should do the trick:
CREATE PROCEDURE zipcode_distance
@ZipStart nchar(5),
@ZipEnd nchar(5),
@Distance real=0 out -- Define an output-variable, and initialise it.
as
... -- Include your procedure body
IF 0 = @Result
SELECT @Distance = dbo.DistanceAssistant(@Lat1,@Long1,@Lat2,@Long2) As Distance
ELSE SELECT @Result AS Distance
RETURN @Result
GO
Usage:
declare @Distance real
exec zipcode_distance @ZipStart, @ZipEnd, @Distance out
I couldn't establish the datatype returned by the function you use, so I suggested "real". You might want to change that.
September 26, 2004 at 2:11 pm
Thanks for your help. - I get "Incorrect Syntax near the word 'AS'" when I changed it to "Select @Distance = ". Any ideas?
Thanks!
September 26, 2004 at 2:36 pm
Sorry, didn't see that one. Just delete the "as Delete" part of the statement, and it should work fine.
September 26, 2004 at 3:12 pm
Thanks again for all of your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply