Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check if cursor exists

Posted on 2006-11-13
6
Medium Priority
?
568 Views
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?
0
Comment
Question by:dbbishop
[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
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17933432
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
 
LVL 16

Accepted Solution

by:
Hillwaaa earned 750 total points
ID: 17933887
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17933925
Doesn't solve the problem - you still have to open the cursor before you can check the status.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Hillwaaa
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!
0
 
LVL 29

Expert Comment

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

Cheers
Night
0
 
LVL 16

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

618 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