Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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