This post is a continuation of Part 11.

We have a table called dbo.Reports. We do not want duplicates in that table. We could run a query to check for duplicates, but we want to ensure that our program never adds rows that cause duplicates to exist in the first place. Before adding rows to our table, we can use a stored procedure that checks to see if duplicates would exist if we added the new rows from the current file. If so, raise and error and roll back all that have done so far.

Another Requirement – No Duplicates

Part 11 did not address the issue of duplicates. We can keep adding the same input file into the database over and over again without any errors. We probably don’t want that. If the four-digit code in the file is a unique report identification code, then it should be unique if we also “combine” it with the Source (company the sends us the file). We need to combine it in case we also import files from other companies and it just so happens that one day two different companies use the same Report Code! If we only focused on the report code, we would erroneously conclude that we have found a duplicate when in fact we have not.

We could check if that code number and Source combination already exists in the Reports table. If so, we can raise a critical error and roll back everything. When would we do this? We don’t extract that particular information until the fourth stored procedure [FromInReportToReports]. What we could do is write a procedure that returns the number of duplicates found or the integer zero if no duplicates were found. We would then raise an critical (ErrorSeverity = 16) error if a non-zero number was found. We could pass in the Source and Code Number to the procedure. The procedure could read the Reports table on it’s own.

[NumberOfDuplicates] is called from [FromInReportToReports]. The two lines of code show how it is called.

DECLARE @duplic INT -- Declare the variable to receive the output value of the procedure.
EXEC dbo.NumberOfDuplicates @source, @codenumber, @numDupReturned = @duplic OUTPUT

Here is some new code for the stored procedure name is NumberOfDuplicates.

-- =============================================================================
-- 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)

		SELECT @numDup = COUNT(*)
			FROM dbo.Reports
			WHERE  @source = SourceCompany AND @codenumber = CodeNumber;
		SET @numDupReturned = @numDup;
		IF @numDup > 0
		BEGIN
			SET @errmsg = 'Adding this file will cause duplicates.'
			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 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