Help with returning value from stored procedure / variables

• John Brasco

  Newbie

  Points: 3

  More actions

  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.

• olavho

  Say Hey Kid

  Points: 706

  More actions

  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.

• John Brasco

  Newbie

  Points: 3

  More actions

  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!

• olavho

  Say Hey Kid

  Points: 706

  More actions

  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.

   

• John Brasco

  Newbie

  Points: 3

  More actions

  September 26, 2004 at 3:12 pm

  #524438

  Thanks again for all of your help.