Improve company productivity with a Business Account.Sign Up

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

Check if cursor exists

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?
0
dbbishop
Asked:
dbbishop
  • 3
  • 3
1 Solution
 
NightmanCTOCommented:
No - you have to use  @@FETCHSTATUS

e.g. OPEN CertCursor
FETCH next from CertCursor into @CertDesc
WHILE @@FETCH_STATUS = 0

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

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

IF @Count>0
BEGIN
DECLARE CertCursor CURSOR FOR SELECT t.CertDesc FROM EmployeeRecords.dbo.tblNew_Hire_Initial_Training t  ...
 etc..
END
0
 
HillwaaaCommented:
Hi dbbishop,

There is a function that does this (see CURSOR_STATUS: http://msdn2.microsoft.com/en-us/library/ms177609.aspx), 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.

Cheers!
0
 
NightmanCTOCommented:
Doesn't solve the problem - you still have to open the cursor before you can check the status.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HillwaaaCommented:
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!
0
 
NightmanCTOCommented:
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 http://www.experts-exchange.com/Community_Support/ to have this re-opened so you can re-assign the points.

Cheers
Night
0
 
HillwaaaCommented:
Nightman - lol - just print off this thread - then you have it in writing :)
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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