Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How not to use cursors
Message
 
To
20/06/2008 09:03:42
Jay Johengen
Altamahaw-Ossipee, North Carolina, United States
General information
Forum:
Microsoft SQL Server
Category:
Scripting
Environment versions
SQL Server:
SQL Server 2000
Miscellaneous
Thread ID:
01325707
Message ID:
01325932
Views:
11
Here one try. Keep in mind that this is NOT tested at all, I don't know nothing about your database, relations between tables. I just see what CURSORS are used and how they are declared. All code here is assumption made. Also because I can not run it on my computer there is possibilities for syntax errors. But you could test it:
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),
	@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),
	@Export_FileName AS VARCHAR(100),
	@Merge_Script AS nvarchar(4000),
	@RTFScript AS nvarchar(4000),
	@Copy_Script AS nvarchar(4000),
	@PageCount AS INTEGER,
	@cmd AS varchar(1000),
	@SourceFileExists AS nvarchar(1),
	@TargetFileExists AS nvarchar(1),
	@ProcessDocument AS nvarchar(1),
	@FirstFile AS VARCHAR(100),
	@ImageRootDirectory AS VARCHAR(100),
	@ImageExportDirectory AS VARCHAR(100),
	@TiffMergerDirectory AS VARCHAR(100),
	@RTFConverterDirectory AS VARCHAR(100),
	@TreatMergeAsCopy AS nvarchar(1)

-- SET @TreatMergeAsCopy = 'N'
SET @TreatMergeAsCopy = 'Y'

SET @ImageRootDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Images\'
SET @ImageExportDirectory = 'G:\Fox_Meadows\Documents\'
SET @TiffMergerDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Tiff\'
SET @RTFConverterDirectory = 'G:\Fox_Meadows\Document_Conversion\ImpactMD\Tiff\'

USE ImpactMD
DECLARE @Charts (ChartId    varchar(30),
                 DocumentId varchar(40),
                 [Path]     varchar(50),
	             PAGE       varchar(10),
                 [Filename] varchar(50),
                 Id int IDENTITY(1,1) PRIMARY KEY)

DECLARE @MinChart int
DECLARE @MaxChart int
INSERT INTO @Charts (ChartId, DocumentId, [Path], Page, [FileName])
SELECT DISTINCT (P.ChartID, H.DocumentId, Documents.[Path], Documents.Page, Documents.[FileName])
	FROM IMPACTMD.dbo.Patients P
	INNER JOIN (SELECT Doc_Header.PatientID,
                       Doc_Header.DocumentId,
                       Doc_Detail.[Path],
                       Doc_Detail.[FileName],
                       Doc_Detail.[Page],
                FROM Doc_Header
                INNER JOIN Doc_Detail ON Doc_Header.DocumentId = Doc_Detail.DocumentId) Documents
    ON Documents.PatientID = P.PatientID

SET @MinChart = 1
SET @MaxChart = SCOPE_IDENTITY()
CREATE TABLE #FolderFiles(s varchar(1000), Prcessed bit DEFAULT(0))
WHILE @MinChart <= @MaxChart
	BEGIN
        -- Get the first ChartId
        SELECT @ChartId    = ChartId
               @documentid = DocumentId
               @PATH       = [Path],
               @Filename   = [FilerName],
               @PAGE       = [Page]
        FROM @Charts WHERE Id = @MinChart

		PRINT ''
		PRINT '---------------------------------------------------------------------'
		PRINT 'ChartID: ' + @ChartID
		PRINT '---------------------------------------------------------------------'

        SELECT @cmd = 'dir /B ' + '"' + @ImageRootDirectory + @Path + @FileName + '"'
		INSERT #FolderFiles (s)
        EXEC master..xp_cmdshell @cmd

		IF EXISTS (SELECT * FROM #FolderFiles WHERE s = @FileName AND Processed = 0)
           BEGIN
               SET @SourceFileExists = 'Y'
               --PRINT 'Source file ' + @ImageRootDirectory + @Path + @FileName + ' exists'
           END
		ELSE
           BEGIN
               SET @SourceFileExists = 'N'
               --PRINT 'Source file ' + @ImageRootDirectory + @Path + @FileName + ' does not exist'
           END

        UPDATE #FolderFiles SET Processed = 1 WHERE Processed = 0

        IF @Filename COLLATE Latin1_General_CI_AI LIKE '%.TIF%' OR 
           @Filename COLLATE Latin1_General_CI_AI LIKE '%.DOC%'
		   BEGIN
               IF @PAGE = 1
                  BEGIN
                      SET @PageCount = 1
                      SET @FirstFile = @ImageRootDirectory + @PATH + @Filename
                      SET @Export_FileName = 'EXP_' + @Filename COLLATE Latin1_General_CI_AI
                      -- Currently G:\TCimages and F:\Images1 on client box
                      SET @Merge_Script = @ImageRootDirectory + @PATH + @Filename + ','
                      SET @Copy_Script = 'copy ' + '"' + @ImageRootDirectory   + @PATH  + @Filename +
                                                 '" "' + @ImageExportDirectory + @Path  + @Export_FileName + '"'

                      SELECT @cmd = 'dir /B ' + '"' + @ImageExportDirectory + @Path + @Export_FileName + '"'
                      INSERT INTO #FolderFiles (s)
                      EXEC master..xp_cmdshell @cmd
                      IF EXISTS (SELECT * FROM #FolderFiles WHERE s = @Export_FileName AND Processed = 0)
                         BEGIN
                             SET @TargetFileExists = 'Y'
                             --PRINT 'Target file ' + @ImageRootDirectory + @Path + @FileName + ' exists'
                             SET @ProcessDocument = 'N' -- Target File Family already exists, so do not process again
                         END
                      ELSE
                         BEGIN
                             SET @TargetFileExists = 'N'
                             --PRINT 'Target file ' + @ImageRootDirectory + @Path + @FileName + ' does not exist'
                         END

		              UPDATE #FolderFiles SET Processed = 1 WHERE Processed = 0
                  END

               IF @PAGE <> 1  AND
                  @Filename COLLATE Latin1_General_CI_AI LIKE '%.TIF%'
                  BEGIN
                      SET @PageCount = @PageCount + 1
                      SET @Merge_Script = @Merge_Script + @ImageRootDirectory + @PATH + @Filename + ','
                  END

                  IF @ProcessDocument = 'Y' AND
                     @TreatMergeAsCopy = 'Y'
                     BEGIN
                          SET @Copy_Script = 'copy ' + '"' + @ImageRootDirectory    + @PATH + @Filename+
                                                     '" "' + @ImageExportDirectory + @Path  +'EXP_' + @FileName + '"'
                          ---PRINT 'COPY: ' + @Copy_Script
                          EXEC MASTER.dbo.xp_cmdshell @Copy_Script, no_output
                     END
        END
        SET @MinChart = @MinChart + 1
    END
DROP TABLE #FolderFiles
SET NOCOUNT OFF
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
Previous
Reply
Map
View

Click here to load this message in the networking platform