Cursor issues

[Delphin]

I do not write cursors very often, but this time I have to. How do you get a nested cursor to work without the error that the cursor already exists or that it is already open?

Here is my code:

	Declare @DT datetime
	DECLARE @apDate nvarchar(20)
	declare @ApName nvarchar (150)
	Declare @ApAge int
	Declare @ApPat bigint
	Declare @apAcct bigint
	declare @ApSt nvarchar(20)
	Declare @ApCh as Char (10)
	Declare @ApIP nvarchar(10)
	Declare @ApUnit nvarchar(10)
	Declare @ApCP nvarchar (15)
	declare @rptdt nvarchar (15)
	set @rptdt = '02/17/05'
	declare @DocID bigint



	


		declare acurDoc cursor for select distinct(dr) from VAppointments where ApptDt = ISNULL(CONVERT(nvarchar, @RptDt, 1), '')

		while @@FETCH_STATUS = 0
			BEGIN
			
			Open AcurDoc
			fetch next from acurdoc into @docid
		select  @docid 
		select @rptdt
	


			



			select Distinct(Starttime), dr, apptdt, Appttime,  [name], age,patientno, accountno, chair,IP, confirmphone, UTP from vappointments where 
			@DT =  ISNULL(CONVERT(nvarchar, @RptDt, 1), '')/* and Dr = @docID*/ order by dr, appttime



			declare acurPat CURSOR for select Distinct(Starttime), dr, apptdt, Appttime,  [name], age,patientno, accountno, chair,IP, confirmphone, UTP from vappointments where 
			@DT =  ISNULL(CONVERT(nvarchar, @RptDt, 1), '')/* and Dr = @docID*/ order by dr, appttime

			open AcurPat


			
			while @@FETCH_STATUS = 0
			BEGIN

			fetch next from acurpat into 
					 @DT,
					@docid,
					 @apdate, 
					 @apname, 
					 @apage, 
					 @appat, 
					 @apacct, 
					 @apst, 
					 @apch,
					 @apip, 
					 @apUnit,
					 @apCP 
				


				select  @DT,
					 @apdate, 
					 @apname, 
					 @apage, 
					 @appat, 
					 @apacct, 
					 @apst, 
					 @apch,
					 @apip, 
					 @apUnit,
					 @apCP 
	
			fetch next from acurpat into 
					 @DT,
					@docid,
					 @apdate, 
					 @apname, 
					 @apage, 
					 @appat, 
					 @apacct, 
					 @apst, 
					 @apch,
					 @apip, 
					 @apUnit,
					 @apCP 
	


			END -- patient loop
			
			close acurpat
			deallocate acurpat

		fetch next from Acurdoc into @docid

			

		END  --Doctor loop
	close acurdoc
	deallocate acurdoc

Have no clue. tried this several different ways but cannot get it to run or select any rows.

 

[Catadmin]

Have you checked your select statements (where you declare the cursor values) in Query Analyzer without the whole "declare cursor" part of it? If your select statements that tell the cursor where to get the data aren't pulling any detail, then neither will your cursor.

Also, why do you have 2 Fetch Nexts in the same (deepest) part of the loop? They look exactly the same to me. I might be wrong, but I would think you only need one of them.

Someone please correct me about this if I'm mistaken, please.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

In your first cursor, you do a @@FETCH_STATUS and then you open the cursor so when it loops it is trying to open it each time. Move your open to beofre your @@FETCH_STATUS. By the way, you will get bombarded qith responses saying cursors are bad performance and should be avoided, so, tell us what you are trying to do and myself or someone may come up with a set-based solution instead.

Tim

 

[Pattycake245]

Catadmin,

You need two fetch next statements for any cursor. The second one will fetch the next record in the cursor and not even go to the top of the cursor and process the first fetch next.

Tim

 

[Delphin]

What I have to do is get a list of all appointments for each doctor. Each of these will be on a separate page. This is part of a report process for this program. (Still trying to figure it out as you have to send each line to the report)

I need a dataset that will give all appointments for the first doctor, then a separate set for the second doctor, and so on for a specific day.

I was hoping to use the cursor to select the doctor, once that happens, open up another one to retrieve the appointments. Close the second and then get the next doctor. I am not sure how I can open a cursor before I get the information from the first cursor as i will not have the information to work with.

I am used to retrieving date and using reports, however, this is the first time I have had to program the report.

 

[donutman]

If you need the cursor to do an Update, Insert or Delete, then you don't need the cursor. What are you trying to accomplish.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

I need a dataset that will give all appointments for the first doctor, then a separate set for the second doctor, and so on for a specific day.

Assuming there are no 5,000,000 appointments for a specific day, you can join both results sets into one denormalized set:

select A.*, B.*
from
( select docID, blah blah for doctors ) A
inner join 
(	select docID, blah blah for appointments ) B
on A.docID=B.docID
order by A.docID, someothercolumns

Then perform client-side grouping. It is piece of cake, and in fact all reporting tools can work the same way. Here is pseudocode (kind of VB-like):

get recordset
do while not rs.Eof
	docID = rs("docID").value
	display heading (doctor)
	
	do while not rs.Eof
		if docID <> rs("docID").value then exit do
		
		display one detail row (appointment)
		rs.moveNext
	loop	
	
	display footer (doctor) if necessary
loop

Note that ORDER BY clause is important, because docID acts as grouping column.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

It is piece of cake...

That's my line. Delphin, sorry I cross-posted with you.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Delphin]

There is no client side grouping... this writes to a tabl;e and is processed line by line from the table

 

[vongrunt]

To a table... processed line by line... please explain.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[Delphin]

each line is written directly to a table which is processed directly to the report. There are no reporting tools on this one.

I would have like Crystal or SQL Reporting services or something, but this is how the client has it working.


In reality after each select in the cursor, a command needs to be entered into the final stored procdure sending the row to be processed. I think the final report is in PCL or something, but the reports are all set up this way.

 

[vongrunt]

Ouch... it hurts.

Then some kind of looping is inevitable. You can eventually select joined results into temp table, then loop over it with cursor (same pseudocode but this time server-side).

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[Catadmin]

PattyCake - Time,

Thank you for the correction. I appreciate it.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!