- SQL Server Exception Handling Introduction
- SQL Server Exception Handling Part 1
- SQL Server Exception Handling Part 2
- SQL Server Exception Handling Part 3
- SQL Server Exception Handling Part 4
- SQL Server Exception Handling Part 5
- SQL Server Exception Handling Part 6
- SQL Server Exception Handling Part 7
- SQL Server Exception Handling Part 8
- SQL Server Exception Handling Part 9
- SQL Server Exception Handling Part 10
- SQL Server Exception Handling Part 11
- SQL Server Exception Handling Part 12
- SQL Server Exception Handling Part 13
- SQL Server Exception Handling Part 14
This post is a continuation of our SQL Server project that uses exception handling in our code to import a text file that is not a delimited file.
In our example project, below is what our input file looks like. ABC Inc. sends us files periodically that have this format. Each time we get this file we need to run a stored procedure, called [AllProcedures] that will import this file into a table in SQL Server called [dbo].[Reports]. We expect the file to be in the same format each time we receive one. We also expect that the four-digit code number is different each time. For this example, we also expect that the file has exactly nine rows. In the real world that number would likely be higher, allowing for more transaction lines. In the real world there would also likely be more information in the file such as the date and more information for each transaction. However, for now, we want to focus on the process and not make things too detailed so as to cloud the important parts of the exercise. In the real world we would use more descriptive names for our tables and stored procedures that we are using here.
Full Listing
This post includes a full SQL Server query listing that will create a new database, tables and stored procedures that allow you to test out this code for yourself. Feel free to make changes to the code and use it freely in your own projects. Part 9 also has a full listing like this one, but that is based on a previous version of the program, I encourage you to use this updated version.
Input Text File (C:\incoming\rpt2.txt)
Report Header from ABC Inc. File name: 123.txt Source: ABC Inc. Four-digit code number: 5578 Transaction number: 12345 Transaction Amount: 12000 Transaction number: 12346 Transaction Amount: 770000 End of Report
SQL Table after importing (dbo.Reports)
Below is a screen shot of the above file’s data after it has been imported into the table dbo.Reports using the sql listing provided below.
Here is a screenshot from SQL Server Management Studio (SSMS).
It shows the database and the table objects and the stored procedures that the below sql script will create for you. As a use of the program you will need to first create the input file in the directory C:\incoming\rpt2.txt. You could change the folder name or place the folder deeper in the hierarchy, but you will need to change the code as well.
Copy this code listing into a new query in SSMS. Execute the query with the Execute button. Don’t worry about the dependency message you get. It will still work. Next, Right click the stored procedure called AllProcedures and select Execute Stored Procedure… and then click the OK button. Now right-click the table dbo.Reports and click Select Top 1000 Rows. You should see the critical contents of your input text file listed in the Results pane of SSMS.
Below is the complete sql listing. It creates a new database, creates the necessary tables and the stored procedures as well. If you wish, feel free to copy this entire listing into a new query in SSMS. Execute the query. Run the stored procedure AllProcedures. You should have imported the file you created in the instructions above.
To copy the listing below, click on the icon with the two blue brackets in the upper right corner of the box that contains the listing. In Windows, click Ctrl+A then Ctrl+C to copy the text. Paste that into a new query window in SSMS and then execute the query. Have a look at the Results pane. Right-click the table dbo.Reports and view the rows in the table with Select Top 1000 Rows, or just write your own select query.
Try to import the same file again and see what message you get. You should get an error that says you would have duplicates.
Try changing the one aspect of the contents of the file and run it again. Try changing the code number. Try changing the code number and the amounts. Notice how delicate the program is.
CREATE DATABASE ImportABCFile
GO
USE ImportABCFile
GO
CREATE TABLE [dbo].[InReport](
[InRows] [varchar](400) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[InReportHistory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SystemDateTime] [datetime] NOT NULL,
[InRows] [varchar](400) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Reports](
[FullFileName] [varchar](50) NULL,
[SourceCompany] [varchar](50) NULL,
[CodeNumber] [varchar](4) NULL,
[TransNumber] [varchar](5) NULL,
[TransAmount] [varchar](8) NULL
) ON [PRIMARY]
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;
DECLARE @rowsread INT;
DECLARE @rowsexpected INT; -- number of rows in the input file
SET @rowsexpected = 9;
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;
END
ELSE
BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION;
END
--
BEGIN TRY
-- Here is where you do your work
BULK INSERT [dbo].[InReport]
FROM 'C:\incoming\rpt2.txt'
WITH
(
DATAFILETYPE = 'char',
--FIELDTERMINATOR = ',', -- we do not need this setting
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 = 'In SProc [BulkInsertFile]: Error. Expecting ' + cast(@rowsexpected as varchar(5)) + ' rows, but ' + cast(@rowsread as varchar(5)) + ' rows were found.';
RAISERROR(@errmsg, 16,1);
END
ELSE
BEGIN
SET @errmsg = 'In SProc [BulkInsertFile]: 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
-- ------------------------------------------------------------------------------------------
BEGIN CATCH
-- 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 Procedure ***';
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;
END
ELSE
BEGIN
-- Procedure must start its own transaction.
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
-- 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 Procedure ***';
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 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;
END
ELSE
BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION;
END
--
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
-- ------------------------------------------------------------------------------------------
BEGIN CATCH
-- 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 Procedure ***';
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;
END
ELSE
BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION;
END
--
BEGIN TRY
-- Here is where we do your work ------------------------------------------------------
DECLARE @rowtext VARCHAR(1000)
DECLARE @filename VARCHAR(50)
DECLARE @source VARCHAR(50)
DECLARE @codenumber VARCHAR(4)
DECLARE @transnumber VARCHAR(10)
DECLARE @transamount VARCHAR(19)
DECLARE @numberdups INT
DECLARE @errmsg VARCHAR(100)
SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%File name:%');
SET @filename = RTRIM(SUBSTRING(@rowtext,12,50))
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,50))
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
--
-- Each company that gives us a file uses a unique code number to identify
-- each report. Therefore if we find that we already have these values in
-- our dbo.InReports table, raise a critical error because we must be running
-- this program a second time for the same file, and we do not want duplicates
-- in our dbo.Reports table. To this end we will call a user-defined function
DECLARE @duplic INT -- Declare the variable to receive the output value of the procedure.
EXEC dbo.NumberOfDuplicates @source, @codenumber, @numDupReturned = @duplic OUTPUT
IF @duplic = 0
BEGIN
SET @errmsg = 'No duplicates will exist after adding this new file.'
RAISERROR(@errmsg, 10, 1) -- Informational message only. No error. Carry on.
END
ELSE
BEGIN
SET @errmsg = 'Has this file already been processed?'
RAISERROR(@errmsg, 16, 1) -- Error.
END
--
-- Let's work on the detail lines, now that we have finished with the header lines.
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
-- ------------------------------------------------------------------------------------------
BEGIN CATCH
-- 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 Procedure ***';
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
-- =============================================================================
-- Author: Mike
-- Create date: March 23, 2017
-- Description: Check for duplicates in the table [dbo.Reports].
-- Return the number of duplicates to the calling routine as an INT.
-- If no duplcates are found, return a zero.
-- The duplicates we are looking for do not already exist in the
-- table dbo.Reports; what consititues a duplicate is if a row is
-- added to dbo.Reports that has the two pieces of data in them
-- that are stored in our two input parameters, a duplicate in the
-- table would exist.
-- Here we raise our own error if there would be duplicates, and the
-- @numDupReturned will never really be returned unless there are
-- no duplicates (@numDupReturned = 0).
-- =============================================================================
CREATE PROCEDURE [dbo].[NumberOfDuplicates]
(
@source VARCHAR(100),
@codenumber VARCHAR(4),
@numDupReturned INT OUTPUT
)
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;
END
ELSE
BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION;
END
--
BEGIN TRY
DECLARE @numDup INT
DECLARE @errmsg VARCHAR(300)
/* Example of DYNAMIC QUERY
-- DECLARE @numDupOUT INT
-- DECLARE @src VARCHAR(100)
-- DECLARE @codenum VARCHAR(4)
-- DECLARE @sqlstring VARCHAR(200)
-- DECLARE @ParmDefinition NVARCHAR(300)
--SET @sqlstring = N'SELECT @numDupOUT = COUNT(*) FROM [ImportFileDemo].[dbo].[Reports]'
--SET @sqlstring = @sqlstring + N'WHERE [SourceCompany] LIKE ''%'
--SET @sqlstring = @sqlstring + @src
--SET @sqlstring = @sqlstring + N'%'' AND [CodeNumber] LIKE '
--SET @sqlstring = @sqlstring + @codenum
--SET @sqlstring = @sqlstring + N'''' -- you escape a single quote by doubling it up
--SET @ParmDefinition = N'@src VARCHAR(100), @codenum VARCHAR(4), @numDupOUT INT OUTPUT';
-- EXECUTE sp_executesql @sqlstring, @ParmDefinition, @src = @source, @codenum = @codenumber, @numDupOUT = @numDup OUTPUT;
--SET @numDupReturned = @numDup
*/
SELECT @numDup = COUNT(*)
FROM dbo.Reports
WHERE @source = SourceCompany AND @codenumber = CodeNumber;
SET @numDupReturned = @numDup;
IF @numDup > 0
BEGIN
SET @errmsg = 'Procedure [NumberOfDuplicates]: Importing this file will cause duplicates in dbo.Reports. File not imported.'
RAISERROR(@errmsg, 16, 1) -- will roll back
END
END TRY
BEGIN CATCH
-- 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 [NumberOfDuplicates], *** 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 [NumberOfDuplicates], *** Rolling Back Procedure ***';
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].[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;
END
ELSE
BEGIN
-- Procedure must start its own transaction.
BEGIN TRANSACTION;
END
--
BEGIN TRY
-- Here is where we do your work -------------------------------------------------------
-- PRINT 'Debugging message - In SProc [AllProcedures], 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;
--
SELECT [FullFileName]
,[SourceCompany]
,[CodeNumber]
,[TransNumber]
,[TransAmount]
FROM [ImportFileDemo].[dbo].[Reports]
END TRY
-- ------------------------------------------------------------------------------------------
BEGIN CATCH
-- 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 Procedure ***';
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
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](400) NULL
) ON [PRIMARY]
DROP TABLE [dbo].[InReportHistory]
CREATE TABLE [dbo].[InReportHistory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SystemDateTime] [datetime] NOT NULL,
[InRows] [varchar](400) NOT NULL
) ON [PRIMARY]
DROP TABLE [dbo].[Reports]
CREATE TABLE [dbo].[Reports](
[FullFileName] [varchar](50) NULL,
[SourceCompany] [varchar](50) NULL,
[CodeNumber] [varchar](4) NULL,
[TransNumber] [varchar](5) NULL,
[TransAmount] [varchar](8) NULL
) ON [PRIMARY]
END
GO
Current Procedure
One improvement to the error trapping code is to use a variable to get the current running stored procedure. The current procedure is stored in @@PROCID. We could use this instead of “hard-coding” the stored procedure name inside of the error message. In this way if we ever need to change the name of the procedure we don’t need to change the error messages. That makes it much easier to maintain the code.
Global Variables
In SQL Server, global variables start with @@. The website Code Project has a very good article on these global variables. Global variables represent a special type of variable. The server always maintain the values of these variables. All the global variables represent information specific to the server or a current user session. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them.