SQL Server Exception Handling Part 9

This entry is part 10 of 15 in the series SQL Server Exceptions
This post is a continuation of Part 8 where we discussed the reason for this project and how this is a combination of two other posts on SQL Server.
We should also do more error checking on the incoming text file. What if the file is empty? What if the file’s format/layout has changed? These two conditions should raise an error and report back to the user why. We will dig deeper into these questions in Part 10 with a discussion of RAISERROR. After discussing RAISERROR in Part 10, Part 11 will re-visit this code below and add some of our own error checking.
Below is a list of all the code you will need to create a new database to see for yourself how this will work. You will also need to create an input file. You can get the file’s contents from Part 8 under the Input File section of the post. Create a new text file with a text editor (Notepad in Windows) and copy the file contents into it. Create a directory called incoming under your C: drive and save that file to that directory, with a file name of rpt2.txt. Alternatively you could modify the code below. Change ‘C:\incoming\rpt2.txt’ in the listing to a drive, folder and filename of your choice. As long as the text contents are the same as what’s found in Part 8, it should work fine. After testing it, you could try modifying the contents of the file and seif you get any errors.
In the code listing below you will see some small icons in the upper right corner of the box. Click the one with the two blue angle brackets to open another window that you can copy, and then paste into a SQL Server Query window in SSMS. From there you can Execute the code. You can change this code if you wish. You can use a different database name if you wish, for example.
CREATE DATABASE ImportFileDemo
GO
USE ImportFileDemo
GO
-- Create Tables =====================================================================
CREATE TABLE [dbo].[InReport](
	[InRows] [varchar](1000) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InReportHistory](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SystemDateTime] [datetime] NOT NULL,
	[InRows] [varchar](1000) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports](
	[FullFileName] [varchar](200) NULL,
	[SourceCompany] [varchar](100) NULL,
	[CodeNumber] [varchar](4) NULL,
	[TransNumber] [varchar](5) NULL,
	[TransAmount] [varchar](8) NULL
) ON [PRIMARY]
GO
-- Create Procedures ====================================================================
CREATE PROCEDURE [dbo].[DeleteRowsInReport] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	    -- Detect whether the procedure was called from an active transaction and save
    -- that for later use. In the procedure, @TranCounter = 0  
    -- means there was no active transaction and the procedure started one. 
    -- @TranCounter > 0 means an active transaction was started before the 
    -- procedure was called.  
    DECLARE @TranCountAtStart INT;  
    SET @TranCountAtStart = @@TRANCOUNT;  
    IF @TranCountAtStart > 0
		BEGIN  
			-- Procedure called when there is an active transaction. 
			-- Create a savepoint to be able to roll back only the work done  
			-- in the procedure if there is an error.
			SAVE TRANSACTION ProcedureSave;
			PRINT 'In SProc [DeleteRowsInReport], @TranCountAtStart is greater than zero; save transaction.'  
        END
	ELSE  
		BEGIN
			-- Procedure must start its own transaction. 
			BEGIN TRANSACTION;
			PRINT 'In SProc [DeleteRowsInReport], @TranCountAtStart is zero; begin transaction';
		END
	--  
    BEGIN TRY 
        -- Here is where we do your work. 
		DECLARE @rowsdeleted AS int;
		DELETE FROM dbo.InReport
		SET @rowsdeleted = @@rowcount;
		PRINT cast(@rowsdeleted as varchar) + ' rows were deleted from table dbo.InReport'
        -- 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
    -- ------------------------------------------------------------------------------------------  
    BEGIN CATCH 
        PRINT 'In SProc [DeleteRowsInReport], An error occurred. We are now inside the CATCH block.' 
        -- An error occurred; must determine which type of rollback will  
        -- roll back only the work done in the procedure. 
        IF @TranCountAtStart = 0
			BEGIN  
				-- Transaction started in procedure. Roll back complete transaction. 
				ROLLBACK TRANSACTION;
				PRINT 'In SProc [DeleteRowsInReport],  ******** Rolling Back Transaction ********';
			END  
        ELSE  
            -- Transaction started before procedure called, do not roll back modifications 
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1
				BEGIN  
					-- If the transaction is still valid, just roll back to the savepoint set at the 
					-- start of the stored procedure.  
					ROLLBACK TRANSACTION ProcedureSave;  
					-- If the transaction is uncommitable, a rollback to the save point is not allowed 
					-- because the save point rollback writes to the log. Just return to the caller, which 
					-- should roll back the outer transaction.
					PRINT 'In SProc [DeleteRowsInReport],  ******** Rolling back to savepoint ********';  
				END

        -- After the appropriate rollback, echo error information to the caller. 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH  
END
GO
-- ====================================================================================
CREATE PROCEDURE [dbo].[BulkInsertFile] 
AS
	BEGIN
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Detect whether the procedure was called from an active transaction and save
    -- that for later use. In the procedure, @TranCounter = 0  
    -- means there was no active transaction and the procedure started one. 
    -- @TranCounter > 0 means an active transaction was started before the 
    -- procedure was called.  
    DECLARE @TranCountAtStart INT;  
    SET @TranCountAtStart = @@TRANCOUNT;  
    IF @TranCountAtStart > 0
		BEGIN  
			-- Procedure called when there is an active transaction. 
			-- Create a savepoint to be able to roll back only the work done  
			-- in the procedure if there is an error.
			SAVE TRANSACTION ProcedureSave;
			PRINT 'In SProc [BulkInsertFile], @TranCountAtStart is greater than zero; save transaction.'  
        END
	ELSE  
		BEGIN
			-- Procedure must start its own transaction. 
			BEGIN TRANSACTION;
			PRINT 'In SProc [BulkInsertFile], @TranCountAtStart is zero; begin transaction';
		END
	--  
    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  */
		)
		-- 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
    -- ------------------------------------------------------------------------------------------  
    BEGIN CATCH 
        PRINT 'In SProc [BulkInsertFile], An error occurred. We are now inside the CATCH block.' 
        -- An error occurred; must determine which type of rollback will  
        -- roll back only the work done in the procedure. 
        IF @TranCountAtStart = 0
			BEGIN  
				-- Transaction started in procedure. Roll back complete transaction. 
				ROLLBACK TRANSACTION;
				PRINT 'In SProc [BulkInsertFile],  ******** Rolling Back Transaction ********';
			END  
        ELSE  
            -- Transaction started before procedure called, do not roll back modifications 
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1
				BEGIN  
					-- If the transaction is still valid, just roll back to the savepoint set at the 
					-- start of the stored procedure.  
					ROLLBACK TRANSACTION ProcedureSave;  
					-- If the transaction is uncommitable, a rollback to the save point is not allowed 
					-- because the save point rollback writes to the log. Just return to the caller, which 
					-- should roll back the outer transaction.
					PRINT 'In SProc [BulkInsertFile],  ******** Rolling back to savepoint ********';  
				END

        -- After the appropriate rollback, echo error information to the caller. 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH  
END
GO
-- ==================================================================================
CREATE PROCEDURE [dbo].[CopyInRepToInRepHist] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Detect whether the procedure was called from an active transaction and save
    -- that for later use. In the procedure, @TranCounter = 0  
    -- means there was no active transaction and the procedure started one. 
    -- @TranCounter > 0 means an active transaction was started before the 
    -- procedure was called.  
    DECLARE @TranCountAtStart INT;  
    SET @TranCountAtStart = @@TRANCOUNT;  
    IF @TranCountAtStart > 0
		BEGIN  
			-- Procedure called when there is an active transaction. 
			-- Create a savepoint to be able to roll back only the work done  
			-- in the procedure if there is an error.
			SAVE TRANSACTION ProcedureSave;
			PRINT 'In SProc [CopyInRepToInRepHist], @TranCountAtStart is greater than zero; save transaction.'  
        END
	ELSE  
		BEGIN
			-- Procedure must start its own transaction. 
			BEGIN TRANSACTION;
			PRINT 'In SProc [CopyInRepToInRepHist], @TranCountAtStart is zero; begin transaction';
		END
	--  
    BEGIN TRY 
        -- Here is where we do your work

		-- The columns of table dbo.InReportHistory are:
		-- Id, SystemDateTime, InRow
		-- Id is an Identity and will automatically increment by 1.
		DECLARE @dt as datetime
		SET @dt = GETDATE()

		INSERT INTO dbo.InReportHistory 
		(SystemDateTime, InRows)
		SELECT @dt, irep.InRows
		FROM dbo.InReport AS irep;

		-- 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
    -- ------------------------------------------------------------------------------------------  
    BEGIN CATCH 
        PRINT 'In SProc [CopyInRepToInRepHist], An error occurred. We are now inside the CATCH block.' 
        -- An error occurred; must determine which type of rollback will  
        -- roll back only the work done in the procedure. 
        IF @TranCountAtStart = 0
			BEGIN  
				-- Transaction started in procedure. Roll back complete transaction. 
				ROLLBACK TRANSACTION;
				PRINT 'In SProc [CopyInRepToInRepHist],  ******** Rolling Back Transaction ********';
			END  
        ELSE  
            -- Transaction started before procedure called, do not roll back modifications 
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1
				BEGIN  
					-- If the transaction is still valid, just roll back to the savepoint set at the 
					-- start of the stored procedure.  
					ROLLBACK TRANSACTION ProcedureSave;  
					-- If the transaction is uncommitable, a rollback to the save point is not allowed 
					-- because the save point rollback writes to the log. Just return to the caller, which 
					-- should roll back the outer transaction.
					PRINT 'In SProc [CopyInRepToInRepHist],  ******** Rolling back to savepoint ********';  
				END

        -- After the appropriate rollback, echo error information to the caller. 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH
END
GO
-- ====================================================================================
CREATE PROCEDURE [dbo].[FromInReportToReports] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	

	-- Detect whether the procedure was called from an active transaction and save
    -- that for later use. In the procedure, @TranCounter = 0  
    -- means there was no active transaction and the procedure started one. 
    -- @TranCounter > 0 means an active transaction was started before the 
    -- procedure was called.  
    DECLARE @TranCountAtStart INT;  
    SET @TranCountAtStart = @@TRANCOUNT;  
    IF @TranCountAtStart > 0
		BEGIN  
			-- Procedure called when there is an active transaction. 
			-- Create a savepoint to be able to roll back only the work done  
			-- in the procedure if there is an error.
			SAVE TRANSACTION ProcedureSave;
			PRINT 'In SProc [FromInReportToReports], @TranCountAtStart is greater than zero; save transaction.'  
        END
	ELSE  
		BEGIN
			-- Procedure must start its own transaction. 
			BEGIN TRANSACTION;
			PRINT 'In SProc [FromInReportToReports], @TranCountAtStart is zero; begin transaction';
		END
	--  
    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) 

		SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%File name:%');
		SET @filename = RTRIM(SUBSTRING(@rowtext,12,100))
		--
		SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%Source:%');
		SET @source = RTRIM(SUBSTRING(@rowtext,9,100))
		--
		SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%code number:%');
		SET @codenumber = SUBSTRING(@rowtext,25,4)

		SELECT * INTO #temptbl
		FROM dbo.InReport
		WHERE InRows LIKE '%Transaction number:%'
	
		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
        /*
		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
    -- ------------------------------------------------------------------------------------------  
    BEGIN CATCH 
        PRINT 'In SProc [FromInReportToReports], An error occurred. We are now inside the CATCH block.' 
        -- An error occurred; must determine which type of rollback will  
        -- roll back only the work done in the procedure. 
        IF @TranCountAtStart = 0
			BEGIN  
				-- Transaction started in procedure. Roll back complete transaction. 
				ROLLBACK TRANSACTION;
				PRINT 'In SProc [FromInReportToReports],  ******** Rolling Back Transaction ********';
			END  
        ELSE  
            -- Transaction started before procedure called, do not roll back modifications 
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1
				BEGIN  
					-- If the transaction is still valid, just roll back to the savepoint set at the 
					-- start of the stored procedure.  
					ROLLBACK TRANSACTION ProcedureSave;  
					-- If the transaction is uncommitable, a rollback to the save point is not allowed 
					-- because the save point rollback writes to the log. Just return to the caller, which 
					-- should roll back the outer transaction.
					PRINT 'In SProc [FromInReportToReports],  ******** Rolling back to savepoint ********';  
				END

        -- After the appropriate rollback, echo error information to the caller. 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH 
END
GO
-- ====================================================================================
CREATE PROCEDURE [dbo].[xDropandCreateAllTables] 
	-- Use this one for debugging purposes.
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DROP TABLE [dbo].[InReport]
	CREATE TABLE [dbo].[InReport](
		[InRows] [varchar](1000) NULL
	) ON [PRIMARY]


	DROP TABLE [dbo].[InReportHistory]
	CREATE TABLE [dbo].[InReportHistory](
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[SystemDateTime] [datetime] NOT NULL,
		[InRows] [varchar](1000) NOT NULL
	) ON [PRIMARY]

	DROP TABLE [dbo].[Reports]
	CREATE TABLE [dbo].[Reports](
		[FullFileName] [varchar](200) NULL,
		[SourceCompany] [varchar](100) NULL,
		[CodeNumber] [varchar](4) NULL,
		[TransNumber] [varchar](5) NULL,
		[TransAmount] [varchar](8) NULL
	) ON [PRIMARY]
END
GO
-- ================================================================================
CREATE PROCEDURE [dbo].[AllProcedures] 
AS
    -- Detect whether the procedure was called from an active transaction and save
    -- that for later use. In the procedure, @TranCounter = 0  
    -- means there was no active transaction and the procedure started one. 
    -- @TranCounter > 0 means an active transaction was started before the 
    -- procedure was called.  
    DECLARE @TranCountAtStart INT;  
    SET @TranCountAtStart = @@TRANCOUNT;  
    IF @TranCountAtStart > 0
		BEGIN  
			-- Procedure called when there is an active transaction. 
			-- Create a savepoint to be able to roll back only the work done  
			-- in the procedure if there is an error.
			SAVE TRANSACTION ProcedureSave;
			PRINT 'In SProc [AllProcedures], @TranCountAtStart is greater than zero; save transaction.'  
        END
	ELSE  
		BEGIN
			-- Procedure must start its own transaction. 
			BEGIN TRANSACTION;
			PRINT 'In SProc [AllProcedures], @TranCountAtStart is zero; begin transaction';
		END
	--  
    BEGIN TRY 
        -- Here is where we do your work -------------------------------------------------------
		PRINT 'In SProc [AllProcedures], We are now inside the BEGIN TRY block.'
		EXEC dbo.DeleteRowsInReport  -- delete rows in table InReport
		EXEC dbo.BulkInsertFile -- import from file to table InReport (one column: InRows)
		EXEC dbo.CopyInRepToInRepHist -- copy to provide an audit trail
		EXEC dbo.FromInReportToReports -- extract data into table dbo.Reports
		-- 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
    -- ------------------------------------------------------------------------------------------  
    BEGIN CATCH 
        PRINT 'In SProc [AllProcedures], An error occurred. We are now inside the CATCH block.' 
        -- An error occurred; must determine which type of rollback will  
        -- roll back only the work done in the procedure. 
        IF @TranCountAtStart = 0
			BEGIN  
				-- Transaction started in procedure. Roll back complete transaction. 
				ROLLBACK TRANSACTION;
				PRINT ' In SProc [AllProcedures],  ******** Rolling Back Transaction ********';
			END  
        ELSE  
            -- Transaction started before procedure called, do not roll back modifications 
            -- made before the procedure was called.  
            IF XACT_STATE() <> -1
				BEGIN  
					-- If the transaction is still valid, just roll back to the savepoint set at the 
					-- start of the stored procedure.  
					ROLLBACK TRANSACTION ProcedureSave;  
					-- If the transaction is uncommitable, a rollback to the save point is not allowed 
					-- because the save point rollback writes to the log. Just return to the caller, which 
					-- should roll back the outer transaction.
					PRINT ' In SProc [AllProcedures], ******** Rolling back to savepoint ********';  
				END

        -- After the appropriate rollback, echo error information to the caller. 
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
    END CATCH  
GO
Series Navigation<< SQL Server Exception Handling Part 8SQL Server Exception Handling Part 10 >>
BeginCodingNow.com

for data analysts & software developers

for data analysts & software developers

SQL Server Exception Handling Part 9
