?
Solved

SImple curosr appears stuck in infinite loop.

Posted on 2014-12-07
2
Medium Priority
?
148 Views
Last Modified: 2014-12-07
SET NOCOUNT ON;

DECLARE @ssn char(9)
DECLARE @Code char(3)
DECLARE @DateEntered char(9)


Hello, I have a simple table with three columns. The table only has like 6 records in it at this time. All I want to do is to read the contents of the table into a cursor, and then dump out the data. I am running the below script from query analyzer, but it appears to be stuck in an infinite loop. Can someone look at my cursor code, and tell me if it is broken?


PRINT '--------  Report --------';

DECLARE people_cursor CURSOR FOR
SELECT SSN, Code,DateEntered
FROM People
ORDER BY DateEntered;

OPEN people_cursor

FETCH NEXT FROM people_cursor
INTO @ssn, @Code, @DateEntered

WHILE @@FETCH_STATUS = 0
BEGIN

      PRINT @ssn
      PRINT @Code
      PRINT @DateEntered

    CLOSE people_cursor
    DEALLOCATE people_cursor

END
0
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 40485539
I see at least 2 issues:
-close/deallocate goes outside the loop
-you are not refetching the next row in the loop

DECLARE people_cursor CURSOR FOR 
SELECT SSN, Code,DateEntered
FROM People
ORDER BY DateEntered;

OPEN people_cursor

FETCH NEXT FROM people_cursor 
INTO @ssn, @Code, @DateEntered

WHILE @@FETCH_STATUS = 0
BEGIN

      PRINT @ssn
      PRINT @Code
      PRINT @DateEntered

     FETCH NEXT FROM people_cursor 
     INTO @ssn, @Code, @DateEntered

END

    CLOSE people_cursor
    DEALLOCATE people_cursor

Open in new window

0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40485551
Thank you Eric,

I am just now learning about cursors.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 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