Solved

Check if cursor exists

Posted on 2006-11-13
6
553 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 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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