Post reply

Help with returning value from stored procedure / variables

  • September 26, 2004 at 12:48 pm

    #60110

    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.

  • September 26, 2004 at 1:07 pm

    #524433

    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

    #524436

    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

    #524437

    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

    #524438

    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