If you move your print statement right before the last END keyword, what would you see?
>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
>
If it's not broken, fix it until it is.
My Blog