Return variable from a Stored Procedure?

I bought a component to do calculations based on Zip code distances, and I'm trying to create a stored procedure out of their sample code to return the distance value. Here's what I have:

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.

Hi Frunobulax,

To get the value of distance back in your ASP page in a variable, you need to add OUTPUT parameter to your stored procedure. For example:

CREATE PROCEDURE zipcode_distance
@ZipStart nchar(5),
@ZipEnd nchar(5),
@Distance varchar(5) OUTPUT

Then, assign whatever is the result to this @Distance variable as:
Set @Distance = @Result

Now, in asp page, pass this new parameter to the stored procedure, but without a value (since this is OUTPUT parameter). Then, once stored procedure is executed, you can access value of this parameter as

cmd.Parameters("@Distance")


Regards,

Vikram