Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

Unable to open cursor created in SQL Express

Hi there,

I'm following the book 'Sams Teach Yourself SQL in 10 Minutes' in Lesson 21 about cursors. I've created the cursor in the book with the code below.

Then I tried to open the cursor as stated in the book using the Open method as below and it gives me an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'cursor'.

So I look on the internet and try just open and the name of the cursor and I get the error:

Msg 16916, Level 16, State 1, Line 1
A cursor with the name 'CustCursor' does not exist.

I declare the cursor again with the same name and I get the error:

Msg 16915, Level 16, State 1, Line 3
A cursor with the name 'CustCursor' already exists.

So what I've figured is that the cursor 'CustCursor' is already created but I can't open it. Can anyone tell me what's going on?

Thanks
declare CustCursor cursor
for
select * from customers
where cust_email is null;
 
 
open cursor CustCursor;
 
 
open CustCursor;

Open in new window

0
longe77
Asked:
longe77
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Your cursor Structure is not complete. A complete cursor statement needs to have Declare, Open, Fetch, Close and Deallocate to avoid these kind of errors. An Example for Complete structure is given below:

DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM Employee_Cursor;
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO

<< A cursor with the name 'CustCursor' already exists.>>

As I mentioned earlier, You have Opened a cursor without closing it. So it is not closed and hence a new cursor can't be opened in the same name. Kindly restart the SQL Services to get it closed out.
0
 
longe77Author Commented:
Thanks rrjegan17.

I finally got it to work. I adapted your code and I inserted a print statement to print the contents of the variable. Well, as I am learning SQL from a book I haven't spotted the significance of using Cursors. I could have obtained the same results using a select statement.

Why would I need a cursor? I understand that a Cursor goes through records line by line.
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now