CODEWITHSUNDEEP
sqlcursor
Robby Johnston
Robby Johnston

Reputation: 191

cursor inside of another cursor not returning correct results

Recently at a site, we had many missing images that I had to recover. The site is a hospital and I was able to recover all of the images and put everything into a temp table that included the affected: encounter, Medical record number, document and subtitle. I am now trying to create one long text file that shows each affected encounter and if there are multiple documents for that one encounter, it will show all of the documents on that encounter. Each affected account will start with %%START in the file and end with %%END. I figured to accomplish this, it would be best to do a cursor to sort through the encounters from this table. THen....cursor through all documents on that encounter to include the encounter. My script looks like this:

declare
@name varchar(40),
@mrn varchar(20),
@encounter varchar(20),
@document varchar(30),
@subtitle varchar(30),
@cmd varchar(255),
@count int

declare find_affected cursor for 
select distinct p.name, m.mrn, m.encounter from mck_hpf..missing_documents m
join his..patients p on m.mrn=p.mrn


open find_affected

fetch next from find_affected into @mrn, @name, @encounter while
@@FETCH_STATUS=0

begin
select @mrn = mrn from mck_hpf.dbo.missing_documents where encounter = @encounter
select @name=name from his.dbo.patients where mrn=@mrn
select @encounter = encounter from mck_hpf.dbo.missing_documents where encounter = @encounter
Print '%%START'
Print ''
Print 'Name:               ' + @name
Print 'MRN:                ' + @mrn
Print 'Encounter:          ' + @encounter
Print 'Possible affected documents in this chart:'

declare find_document cursor for 
select distinct(document), subtitle from mck_hpf..missing_documents m
join his..encounters e on e.encounter=m.encounter where @mrn=m.mrn and @encounter=m.encounter

open find_document
fetch next from find_document into @document, @subtitle 
while @@FETCH_STATUS=0
begin
select @document = document from mck_hpf.dbo.missing_documents where encounter = @encounter 
select @subtitle = subtitle from mck_hpf.dbo.missing_documents where encounter = @encounter and document=@document
Print @document + '-' + @subtitle
fetch next from find_document into @document, @subtitle 
end
close find_document
deallocate find_document
Print ''
Print ''
Print '%%END'


fetch next from find_affected into @mrn, @name, @encounter
end
close find_affected
deallocate find_affected
go

The script works good and cycles through the encounters correctly.....but for some reason it is not working on the documents. Example....encounter 123 has 2 documents in the missing_documents table lets say called UB04 and 1500....when the script is done, instead of saying:

Name:               Mickey Mouse                           
MRN:                11111111          
Encounter:          123456789

Possible affected documents in this chart:

UB04                             
1500

It will say:

Name:               Mickey Mouse                           
MRN:                11111111          
Encounter:          123456789

Possible affected documents in this chart:

UB04                             
UB04

It is like the cursor knows that there are 2 missing documents, but it repeats the first one it finds over and over. IT does this for all of the encounters......if there are 6 missing docs, it picks up the first one and continually repeats itself.

IT took me awhile to get to this point and get it to work and I just dont know where to go from here as I thought my code looks correct. I searched on here but didnt find anything that could help me. Any help would be appreciated. THank you kindly

Upvotes: 1

Views: 92

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You are resetting @subtitle right above the print statement . . .

select @document = document from mck_hpf.dbo.missing_documents where encounter = @encounter 
select @subtitle = subtitle from mck_hpf.dbo.missing_documents where encounter = @encounter and document=@document
Print @document + '-' + @subtitle

If you want to see the values from the second cursor, do the print before the selects:

Print @document + '-' + @subtitle;
select @document = document from mck_hpf.dbo.missing_documents where encounter = @encounter ;
select @subtitle = subtitle from mck_hpf.dbo.missing_documents where encounter = @encounter and document=@document;

Upvotes: 1

Related Questions