Solved

Check if cursor exists

Posted on 2006-11-13
6
546 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
  • 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 250 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now