1) ALWAYS USE BOL!!! It is an AWESOME resource. I NEVER have it closed, and I have been working with SQL Server for 15+ years! 🙂

2) Your sample and sproc had numerous problems. This should get you most of the way there:

ALTER PROCEDURE InsertBatches

@FileNameVARCHAR(256) = NULL

, @FilePathVARCHAR(1024) = NULL

, @cDBConnection VARCHAR(256) = NULL

, @rDBConnection VARCHAR(256) = NULL

, @CustomerIDINT

, @ImportTFBIT

, @cpTFBIT

, @FilterTFBIT

, @MatchTFBIT

, @exportTFBIT

, @InitialCutoffDate DATETIME

, @TerminalCutoffDate DATETIME

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

;MERGE Batches AS b

USING (VALUES (@FileName, @cDBConnection)) AS o (FileName, cDBConnection)

ON b.FileName = o.FileName AND b.cDBConnection = o.cDBConnection

WHEN NOT MATCHED BY TARGET THEN

INSERT (FileName, cDBConnection, rDBConnection, CustomerID , ImportTF, cpTF, FilterTF, MatchTF, exportTF, InitialCutoffDate, TerminalCutoffDate, StatusID, StartDateTime, EndDateTime)

VALUES (@FileName, @cDBConnection, @rDBConnection, @CustomerID , @ImportTF, @cpTF, @FilterTF, @MatchTF, @exportTF, @InitialCutoffDate, @TerminalCutoffDate, 0, GETDATE(), GETDATE());

COMMIT TRANSACTION

END TRY

BEGIN CATCH

DECLARE @ErrorMessage VARCHAR(500)

SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback InsertBatches '

Print @ErrorMessage

ROLLBACK TRANSACTION

RAISERROR (@ErrorMessage, 16,1)

END CATCH

END

GO

BEGIN TRAN

EXEC InsertBatches @FileName= 'af', @CustomerID = 9999, @ImportTF = 1, @cpTF = 1, @FilterTF = 1, @MatchTF = 1, @exportTF = 1, @InitialCutoffDate='1/1/2020', @TerminalCutoffDate='1/1/2020';

SELECT *

FROM BATCHES

WHERE filename = 'af'

ROLLBACK TRAN