I am loading the contents of an EXCEL workbooks into SQL database tables. Some of these workbooks have worksheets called EquipmentWarranty and Warranty, and some have just Warranty. I would like to avoid having to have the user flip back an forth between two different scripts, but every time I run the code that contains code for both within IF statements, it stops when it checks the section that deals with workbooks having both worksheets and declares an error. I have also tried this with separate IF statements as well as labels and GOTO statements. Nothing seems to work.
Code:
DECLARE @EquipWarrantyPresent BIT
SET @EquipWarrantyPresent = 0 -- 1 = EquipmentWarranty exists - 0 = EquipmentWarranty does not exist
IF @EquipWarrantyPresent = 1
BEGIN
BEGIN TRY
INSERT INTO dbo.Warranty
(
fldEquipmentTagID,
fldEquipmentType,
fldManufacturer,
fldModel,
fldWarrantyNumber,
fldStartDate,
fldEndDate,
fldRenewalOptions,
fldcompany,
fldcontactname,
fldAddressStreet,
fldAddressCity,
fldAddressStateProvince,
fldAddressCountry,
fldContactPhone,
fldContactEMail,
fldRemarks
)
SELECT
ew.EquipmentTagID,
ew.EquipmentType,
ew.Manufacturer,
ew.Model,
ew.WarrantyNumber,
w.StartDate,
w.EndDate,
w.RenewalOptions,
w.Company,
w.ContactName,
w.AddressStreet,
w.AddressCity,
w.AddressStateProvince,
w.AddressCountry,
w.ContactPhone,
w.ContactEMail,
w.remarks
FROM CMMSSrc...[EquipmentWarranty$]ew
INNER JOIN CMMSSrc...[Warranty$] w
ON ew.fldWarrantyNumber = w.fldWarrantyNumber
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
IF ERROR_NUMBER() = 7399
BEGIN
SELECT 'Check file name CAREFULLY! Check underscores!'
END
END CATCH
END
ELSE
BEGIN
BEGIN TRY
INSERT INTO dbo.Warranty
SELECT *
FROM CMMSSrc...[Warranty$]
WHERE [Equipment Tag ID] is NOT NULL
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
IF ERROR_NUMBER() = 7399
BEGIN
SELECT 'Check file name CAREFULLY! Check underscores!'
END
END CATCH
END