This post is a continuation of all of what was discussed in parts 8, 9 and 10. Now we are making the third last set of changes to our file import program with exception handling. The next post, Part 12, will discuss the checking of duplicates.

Adding our own Error Checking

In this post we are adding our own error checking routines. For example, we want to be sure that the file we are importing is in good condition. There must be the correct number of data rows in the incoming file. Of course, the file must exist in the first place. SQL Server will return its own error if we try to insert data from a file that SQL Server cannot find. However, we need to tell SQL Server, with our own errors using RAISERROR when we have not received the correct number of rows from the input file if that is the case. We also need to find all of the data “fields” in the input file as well. That checking is done in the last stored procedure [FromInReportToReports].

The stored procedure [AllProcedures] simply runs the following stored procedures in order. Below that is our new partial code listing. For brevity, we only show the changed code. That code is between the BEGIN TRY and END TRY blocks.

1. EXEC dbo.DeleteRowsInReport — delete rows in table InReport
2. EXEC dbo.BulkInsertFile — import from file to table InReport (one column: InRows)
3. EXEC dbo.CopyInRepToInRepHist — copy to provide an audit trail
4. EXEC dbo.FromInReportToReports — extract data into table dbo.Reports

[DeleteRowsInReport]

    BEGIN TRY 
        -- Here is where we do your work. 
		DECLARE @rowsdeleted AS INT;
		DELETE FROM dbo.InReport
		SET @rowsdeleted = @@rowcount;
		IF @rowsdeleted = 0
		BEGIN
			-- raise an error. Non-critical. Informational only. Do not jump to CATCH.
			DECLARE @errMsg AS VARCHAR(100)
			SET @errMsg = 'No records deleted from table dbo.InReport because table was empty.';
			RAISERROR(@errMsg, 10,1) WITH NOWAIT;
			-- Remember: When RAISERROR is run with a severity of 11 or higher in a TRY block
			-- it transfers control to the associated CATCH block. 
		END
        -- End of our work.	    
		
		-- Get here if no errors; must commit any transaction started in the 
        -- procedure, but not commit a transaction started before the transaction was called.
        IF @TranCountAtStart = 0  
            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called. 
            -- The procedure must commit the transaction it started. 
            COMMIT TRANSACTION;  
    END TRY

[BulkInsertFile]

At the top of the stored procedure we added two lines of code to declare and set the variable @rowsexpected. In the code listing below, we have added the code starting with the comment that begins with “What if” and ends with the line above the comment “end of our work”. We raise an error if we do not read in 9 rows from the file. If its not 9 rows then either the user didn’t copy the data to the file correctly, or the file format changed. In either case the entire set of procedures need to be rolled back. We raise an error with a level of 16 to ensure we jump to the CATCH block, which will raise its own error that the caller, AllProcedures will receive and it will roll back the transaction.

	DECLARE @rowsexpected INT;  -- number of rows in the input file
	SET @rowsexpected = 9;

    BEGIN TRY 
        -- Here is where you do your work 
		PRINT 'In SProc [BulkInsertFile], We are now inside the BEGIN TRY block.'
		BULK INSERT [dbo].[InReport]
		FROM 'C:\incoming\rpt2.txt'
		WITH
		(
		DATAFILETYPE = 'char',
		--FIELDTERMINATOR = ',', 
		ROWTERMINATOR = '0x0a', 
		FIRSTROW = 1   /* Start at row 1  */
		)
		-- What if the file rpt2.txt is completely empty?
		-- What if the report text is only partially there? There should be
		-- nine (9) rows affected.
		-- Anything other than 9 rows imported consitutes a critical error 
		-- and we need to roll back the transaction
		SET @rowsread = @@ROWCOUNT;
		IF @rowsread <> @rowsexpected
		BEGIN
			DECLARE @errmsg AS VARCHAR(200);
			SET @errmsg = 'Error. Expecting ' + cast(@rowsexpected as varchar(5)) + ' rows, but ' + cast(@rowsread as varchar(5)) + ' rows were imported into [dbo].[InReport].';
			RAISERROR(@errmsg, 16,1);
		END
		ELSE 
			BEGIN
				SET @errmsg = 'Read ' + cast(@rowsread as varchar(5)) + ' rows into [dbo].[InReport]';
				RAISERROR(@errmsg, 10,1);
			END
		-- End of our work.

       IF @TranCountAtStart = 0  
            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called. 
            -- The procedure must commit the transaction it started. 
            COMMIT TRANSACTION;  
    END TRY

[CopyInRepToInRepHist]

For this stored procedure we are not going to need to add any more error checking. All this one does is copy from the InReport table to the InReportHistory table and adds the system date and time to a column in the InReportHistory table using SQL’s INSERT INTO SELECT statement.

[FromInReportToReports]

Here is where most of the work of the whole program is done. Normally in the real world there would be a lot more work to do here. The file would have more information in it. Also the file would likely have its own checks and balances. For example, it would have the number of transactions and the total amount of the transactions. We would want to read those in and check that the total does equal the reported total. Any discrepancies would cause a critical error that would cause the transaction to roll back to the beginning.

    BEGIN TRY 
        -- Here is where we do your work ------------------------------------------------------
		DECLARE @rowtext as varchar(1000)
		DECLARE @filename as varchar(100)
		DECLARE @source as varchar(50)
		DECLARE @codenumber as varchar(4)
		DECLARE @transnumber as varchar(10)
		DECLARE @transamount as varchar(19)
		DECLARE @errmsg VARCHAR(100); 

		SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%File name:%');
		SET @filename = RTRIM(SUBSTRING(@rowtext,12,100))
		IF LEN(@filename) = 0
			BEGIN
				SET @errmsg = 'No File Name found. Terminating and rolling back.'
				RAISERROR(@errmsg, 16, 1) -- will roll back
			END
		--
		SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%Source:%');
		SET @source = RTRIM(SUBSTRING(@rowtext,9,100))
		IF LEN(@source) = 0
			BEGIN
				SET @errmsg = 'No Source found. Terminating and rolling back.'
				RAISERROR(@errmsg, 16, 1) -- will roll back
			END
		--
		SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%code number:%');
		SET @codenumber = SUBSTRING(@rowtext,25,4)
		IF LEN(@codenumber) = 0
			BEGIN
				SET @errmsg = 'No Code Number found. Terminating and rolling back.'
				RAISERROR(@errmsg, 16, 1) -- will roll back
			END
		--
		DECLARE @rowsread AS INT;
		SELECT * INTO #temptbl
		FROM dbo.InReport
		WHERE InRows LIKE '%Transaction number:%'
		-- if no rows were added to temp table, then no transaction numbers found
		-- and that is a critical error, therefore raiserror and roll back
		SET @rowsread = @@ROWCOUNT;
		IF @rowsread = 0
			BEGIN
				SET @errmsg = '0 transaction numbers found. Terminating and rolling back.'
				RAISERROR(@errmsg, 16, 1) -- will roll back
			END
		ELSE
			BEGIN
				SET @errmsg = CAST(@rowsread AS VARCHAR(5)) + ' transaction numbers found.'
				RAISERROR(@errmsg, 10, 1) -- Informational message only. No error. Carry on.
				WHILE (SELECT COUNT(*) FROM #temptbl) > 0
					BEGIN
					SELECT TOP 1 @rowtext = InRows FROM #temptbl
					SET @transnumber = SUBSTRING(@rowtext,21,5)
					SET @transamount = LTRIM(SUBSTRING(@rowtext,49,8))
					INSERT INTO dbo.Reports (FullFileName, SourceCompany, CodeNumber, TransNumber, TransAmount)
					VALUES (@filename, @source, @codenumber, @transnumber, @transamount);
					DELETE #temptbl Where InRows = @rowtext
					END
			END
        /*
		PRINT 'To debug, force divide by zero error here in SProc [FromInReportToReports]';
		DECLARE @bad as int;
		SET @bad = 5/0;
		*/

		-- End of our work ---------------------------------------------------------------------

	    -- Get here if no errors; must commit any transaction started in the 
        -- procedure, but not commit a transaction started before the transaction was called.
        IF @TranCountAtStart = 0  
            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called. 
            -- The procedure must commit the transaction it started. 
            COMMIT TRANSACTION;  
    END TRY