Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cursor already open Error

Posted on 2005-05-10
3
Medium Priority
?
575 Views
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
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
0
Comment
Question by:mbart
3 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13971153
Try adding the LOCAL clause in your cursor declaration:

Declare Distance Cursor Local for
0
 
LVL 7

Accepted Solution

by:
ABaruh earned 1200 total points
ID: 13971163
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
 

Author Comment

by:mbart
ID: 13971486
Exactly what I needed... thanx so much for the quick response
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.
Suggested Courses

810 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