Cursor already open Error

Here is my SQL 2000 stored procedure  This is my first attempt so don't laugh.  When I try to execute it I get the following error:
A cursor with the name 'Distance' already exists from line 24  Is this caused because of the Create Table statement?  I have seen other code which was done that way and it worked.
Need fast help

CREATE Procedure SLATER_Reverse_Suggest (@seltank varchar(2), @NRA varchar(4)) AS
/*  This procedure does the processing work for the "Distance" option of the */
/*  suggest_billets web page.  Processing goes like this:  */
/* 1. go thru all the SSN's and calculate the distance between the nra's zipcode and applicant's zipcode  */
/* 2. put the qualifying SSN's in a temporary table, #Distance */
/* 3. join #Distance with the Reverse_Suggest and the seltank passed parameter */
/* 5. drop the temp tables */
/*******************************************************************************************************************/
Set NoCount On
Declare @zip varchar(5),
 @zip_base varchar(5),
@ssn char(9),
 @dist float
Select @zip_base=(select zip from NRA where NRA=@NRA)  
print 'Getzip for NRA'
/* Create the temp table */
Create Table #Distance
 (ssn varchar(4),
  zip varchar(5),
  dist float)
print 'create temp tables'
/* Setup a cursor to fill up the #Distance table */
Declare Distance Cursor for
 Select ssn, zip from Applicant
Open Distance
Fetch Distance into @ssn,@zip
While @@fetch_status = 0
Begin
 exec zip_distance @zip_base, @zip, @dist output
 insert into #Distance (ssn, zip, dist)
  Values (@ssn, @zip, @dist)
 Select @dist = Null
Fetch Distance into @ssn,@zip
End
Close Distance
Deallocate Distance
print 'fill Distance table'
Insert into #Reverse_Suggest
 (Applicant_Id, ssn, lastname, firstname, desig, rankcode, travel_distance, tankid  )
 Select a.applicant_ID, a.ssn, a.lastname, a.firstname, a.desig, a.rankcode, a.travel_distance, u.status
 from applicant a
 join users u on a.ssn = u.password
 join distance d on d.ssn = a.ssn
 where  status = '1' and tankID = @seltank
Set NoCount Off
/*output the results */
select * from  #reverse_suggest
Drop table #Distance
GO
mbartAsked:
Who is Participating?
 
ABaruhCommented:
you need:

fetch next from distance into @ssn, @zip

not

fetch distance into @ssn, @zip

Before you try and execute the query again, run this:

Close Distance
Deallocate Distance
0
 
rafranciscoCommented:
Try adding the LOCAL clause in your cursor declaration:

Declare Distance Cursor Local for
0
 
mbartAuthor Commented:
Exactly what I needed... thanx so much for the quick response
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.