There are many ChartID values in the ChartID cursor, but this is only using the first one in the FETCH. The results all have a ChartID of 1. If I run the SQL that creates the ChartID cursor separately, it shows many ChartID rows. What am I missing?
SET NOCOUNT ON
DECLARE
@poscode AS VARCHAR(10),
@posdesc AS VARCHAR(100),
@acctno AS VARCHAR(20),
@provcode AS VARCHAR(20),
@PatientID AS VARCHAR(25),
@ChartNumber AS VARCHAR(30),
@ChartID AS VARCHAR(30),
@Last_Name AS VARCHAR(30),
@First_Name AS VARCHAR(30),
@Middle_Initial AS VARCHAR(5),
@Sex AS CHAR(1),
@DOB AS CHAR(10),
@SSN AS CHAR(11),
@ID AS VARCHAR(20),
@LastName AS VARCHAR(30),
@FirstName AS VARCHAR(30),
@MiddleInitial AS VARCHAR(5),
@TITLE AS VARCHAR(10),
@Suffix AS VARCHAR(10),
@UPIN AS VARCHAR(20),
@NAME AS VARCHAR(100),
@LocID VARCHAR(20),
@DocDateTime AS VARCHAR(8),
@DocumentName AS VARCHAR(80),
@OldDocDateTime AS VARCHAR(22),
@OldSubject AS VARCHAR(80),
@Filename AS VARCHAR(50),
@PATH AS VARCHAR(50),
@PAGE AS VARCHAR(10),
@documentid AS VARCHAR(40),
@SQL AS VARCHAR(8000),
@COPYSCRIPT AS nvarchar(4000),
@CREATEDIRSCRIPT AS nvarchar(4000),
@DELETEDIRSCRIPT AS nvarchar(4000),
@Folder AS VARCHAR(100),
@Export_FileName AS VARCHAR(100),
@Merge_Script AS nvarchar(4000)
USE ImpactMD
DECLARE
ChartID CURSOR
Forward_Only
FOR
SELECT DISTINCT (P.ChartID)
FROM IMPACTMD.dbo.Patients P
INNER JOIN IMPACTMD.dbo.Doc_Header H ON H.PatientID = P.PatientID
OPEN ChartID
FETCH NEXT
FROM ChartID
INTO @ChartID
WHILE @@FETCH_STATUS = 0
PRINT @ChartID
BEGIN
DECLARE Patient CURSOR
Forward_Only
Read_Only
FOR
SELECT DISTINCT
ISNULL(CONVERT(VARCHAR(8),H.DATE,112),'') AS DocumentDate,
ISNULL(H.DocumentName,'') ,
ISNULL(H.documentid,'') ,
ISNULL(FT.DESCRIPTION,'')
FROM IMPACTMD.dbo.Patients P
INNER JOIN IMPACTMD.dbo.Doc_Header H
ON H.PatientID = P.PatientID
INNER JOIN IMPACTMD.dbo.Folder_Template FT
ON H.FolderID = FT.FolderID
WHERE P.ChartID = @ChartID
OPEN Patient
FETCH NEXT
FROM Patient
INTO
@DocDateTime ,
@DocumentName,
@documentid ,
@Folder
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE PAGE CURSOR
Forward_Only
Read_Only
FOR
SELECT
DD.PATH,
DD.Filename,
DD.PAGE
FROM IMPACTMD.dbo.Doc_Detail DD
WHERE documentid = @documentid
ORDER BY DD.Filename, DD.PAGE
OPEN PAGE
FETCH NEXT
FROM PAGE
INTO
@PATH ,
@Filename,
@PAGE
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PAGE = 1
BEGIN
SET @Export_FileName = @Filename
SET @Merge_Script = 'c:\ImpactMD\Images\' + @PATH + @Filename + ','
END
IF @PAGE <> 1
BEGIN
SET @Merge_Script = @Merge_Script + 'c:\ImpactMD\Images\' + @PATH + @Filename + ','
END
FETCH NEXT
FROM PAGE
INTO
@PATH ,
@Filename,
@PAGE
END
CLOSE PAGE
DEALLOCATE PAGE
SET @Merge_Script = LEFT(@Merge_Script,LEN(LTRIM(RTRIM(@Merge_Script))) -1)
SET @Merge_Script = @Merge_Script + '|c:\ImpactMD\Export|' + @Export_FileName
SET @Merge_Script = 'C:\ImpactMD\Tiff\Tiff_Merger.exe "' + @Merge_Script + '"'
FETCH NEXT
FROM Patient
INTO
@DocDateTime ,
@DocumentName,
@documentid ,
@Folder
END
CLOSE Patient
DEALLOCATE Patient
FETCH NEXT
FROM ChartID
INTO
@ChartID
END
CLOSE ChartID
DEALLOCATE ChartID
SET NOCOUNT OFF