Check if cursor exists

Posted on 2006-11-13
Last Modified: 2012-08-14
I have a procedure that has, in part, the following code:

DECLARE CertCursor CURSOR FOR SELECT t.CertDesc FROM EmployeeRecords.dbo.tblNew_Hire_Initial_Training t  ...

Is there syntax I can use afterwards to insure I have data in the cursor before trying to open it and fetch the first row?
Question by:dbbishop
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
  • 3
  • 3
LVL 29

Expert Comment

ID: 17933432
No - you have to use  @@FETCHSTATUS

e.g. OPEN CertCursor
FETCH next from CertCursor into @CertDesc

Alternatively, you can select count into a local variable first, and then check the value of @Count

declare @Count int
SELECT @Count=COUNT(*) FROM EmployeeRecords.dbo.tblNew_Hire_Initial_Training t  ...

IF @Count>0
DECLARE CertCursor CURSOR FOR SELECT t.CertDesc FROM EmployeeRecords.dbo.tblNew_Hire_Initial_Training t  ...
LVL 16

Accepted Solution

Hillwaaa earned 250 total points
ID: 17933887
Hi dbbishop,

There is a function that does this (see CURSOR_STATUS:, however it will only indicate if there is data for cursors that have been declared as variables - see the example at the bottom of the page.

LVL 29

Expert Comment

ID: 17933925
Doesn't solve the problem - you still have to open the cursor before you can check the status.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 16

Expert Comment

ID: 17934029
Nightman - true - you are right on that point (as always :)

ddbishop - there really doesn't appear to be a way to determine how many rows are in a cursor until it is opened.  CURSOR_STATUS, @@CURSOR_ROWS (which would also work) and @@FETCHSTATUS all require an open cursor.  

Therefore you should probably refund those points to Nightman - he answered your question more correctly!
LVL 29

Expert Comment

ID: 17934064
Hillwaa - please let my wife know that I'm usually right. I have been trying to convince her for 14 years now, and making no progress whatsoever ;o)

ddbishop - you can post a request in to have this re-opened so you can re-assign the points.

LVL 16

Expert Comment

ID: 17934078
Nightman - lol - just print off this thread - then you have it in writing :)

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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