T-SQL Code: Another Example of the use of a Cursor; Parsing WebCom Hit Log Files; GetHitsImport

CREATE PROCEDURE GetHitsImport ( @errorCount int OUTPUT ) AS

-- Description: Transforms data in tblLogImport into data for tblLog. -- Developer: rasx

DECLARE @logEntry varchar(1000) , @type varchar(8) , @date datetime , @address varchar(255) , @file varchar(255) , @entryValid bit , @charPosStart int , @charPosEnd int , @srchStr varchar(255)

SET NOCOUNT ON

-- Record last log entry date. INSERT INTO tblLogDates SELECT MAX([date]) FROM tblLog

SET @errorCount = 0

DECLARE tblLogImportCursor CURSOR FOR SELECT LogEntry FROM tblLogImport

OPEN tblLogImportCursor

FETCH NEXT FROM tblLogImportCursor INTO @logEntry

WHILE @@FETCH_STATUS = 0 BEGIN IF LEN(@logEntry) > 0 AND LEFT(@logEntry,1) <> '#' BEGIN SET @entryValid = 1 SET @type = LTRIM(SUBSTRING(@logEntry,1,3)) SET @date = LTRIM(SUBSTRING(@logEntry,5,19))

    SET @srchStr = SPACE(1) + '/'
    EXECUTE GetcharPos
            @str        = @logEntry
        ,   @srchstr    = @srchStr
        ,   @iterations = 0
        ,   @pos        = @charPosEnd OUTPUT
    IF @charPosEnd &gt; 25
    BEGIN
        SET @address = LTRIM(SUBSTRING(@logEntry,25,@charPosEnd - 25))
    END
    ELSE
    BEGIN
        SET @entryValid = 0
        SET @errorCount = @errorCount + 1
    END
    
    SET @charPosStart = @charPosEnd
    EXECUTE GetcharPos
            @str        = @logEntry
        ,   @srchstr    = '.'
        ,   @iterations = 0
        ,   @pos        = @charPosEnd OUTPUT
    
    SET @charPosEnd = @charPosEnd + 5
    IF @charPosStart &gt;= @charPosEnd
    BEGIN
        EXECUTE GetcharPos
                @str        = @logEntry
            ,   @srchstr    = '/'
            ,   @iterations = 0
            ,   @pos        = @charPosEnd OUTPUT
        SET @charPosEnd = @charPosEnd + 1
    END

    IF @charPosEnd &gt; @charPosStart
    BEGIN
        SET @file = LTRIM(SUBSTRING(@logEntry,@charPosStart,@charPosEnd - @charPosStart))
    END
    ELSE
    BEGIN
        SET @entryValid = 0
        SET @errorCount = @errorCount + 1
    END
END
ELSE
BEGIN
    SET @entryValid = 0
    SET @errorCount = @errorCount + 1
END

IF @entryValid = 1
BEGIN
    INSERT INTO tblLog
    ([type], [date], address, [file])
    VALUES(@type, @date, @address, @file)
END

FETCH NEXT FROM tblLogImportCursor
INTO @logEntry

END

CLOSE tblLogImportCursor DEALLOCATE tblLogImportCursor

mod date: 2001-03-04T18:06:53.000Z