Cursor already open Error

Posted on 2005-05-10
Last Modified: 2012-06-21
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
 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
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
Question by:mbart
    LVL 28

    Expert Comment

    Try adding the LOCAL clause in your cursor declaration:

    Declare Distance Cursor Local for
    LVL 7

    Accepted Solution

    you need:

    fetch next from distance into @ssn, @zip


    fetch distance into @ssn, @zip

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

    Close Distance
    Deallocate Distance

    Author Comment

    Exactly what I needed... thanx so much for the quick response

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now