Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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