Unable to insert the records in the tables in SQL server

Question

This is my store procedure, when I am executing it for testing , it says records inserted successfully, but I cannot see any records in the table. Data is inserted into 3 tables- Shipment , Ship_commodity and Commodity_Hazmat. These 3 tables are inter connected. id from shipment table=shipment id from Ship_commodity
id from Ship_commodity table=commodity id of Commodity_Hazmat table

CREATE PROC [dbo].[usp_InsertBulk]
(       
@ShipmentType       VARCHAR(10),
@SCACCode           VARCHAR(4),
@SCNumber           VARCHAR(12),
@CountryLoading     VARCHAR(2),
@PortLoading        VARCHAR(21),    
@ShipperName        VARCHAR(60),    
@ShipperAddress     VARCHAR(100),
@ShipperCity        VARCHAR(50),
@ShipmentCountry    VARCHAR(2),
@ShipmentProvince       VARCHAR(3),
@ShipperZip         VARCHAR(15),
@ShipperPhone       VARCHAR(20),
@ShipperEmail       VARCHAR(100),

@ConsigneeName      VARCHAR(60),    
@ConsigneeAddress   VARCHAR(100),
@ConsigneeCity      VARCHAR(50),    
@ConsigneeCountry   VARCHAR(2),
@ConsigneeState VARCHAR(3), 
@ConsigneeZip       VARCHAR(15),
@ConsigneePhone     VARCHAR(20),
@ConsigneeEmail     VARCHAR(100),

@Description        VARCHAR(90),
@Quantity           INT,
@QuantityUnit       VARCHAR(3), 
@Weight             INT,
@WeightUnitCode     VARCHAR(1),
@Countrycode        VARCHAR(2),
@Value              VARCHAR(8),
@HarmonizedCode     VARCHAR(30),
@MarksNumbers       VARCHAR(48),
@CmdHMCode1         VARCHAR(10),
@CmdHMName1         VARCHAR(24),    
@CmdHMPhone1        VARCHAR(256),
@CmdHMCode2         VARCHAR(10),
@CmdHMName2         VARCHAR(24),
@CmdHMPhone2        VARCHAR(256),
@CmdHMCode3         VARCHAR(10),
@CmdHMName3         VARCHAR(24),
@CmdHMPhone3        VARCHAR(256),
@CustomControl      VARCHAR(50),
@DateShipmentLeft   VARCHAR(50),
@InbondEntryType    VARCHAR(2),
@InbondDestination  VARCHAR(5),
@OnwardCarrier      VARCHAR(4),
@BondedCarrier      VARCHAR(12),
@Inbond             VARCHAR(9),
@TransferCarrier    VARCHAR(12),
@ForeignPortDestination VARCHAR(12),
@EstimatedDeparture VARCHAR(8),
@MexiPediNumber     VARCHAR(12),
@RetVal             VARCHAR(1000) OUTPUT

)

AS
BEGIN
DECLARE @CMDId BIGINT
DECLARE  @Id                    BIGINT
DECLARE @ShipmentID         BIGINT
BEGIN TRY   
BEGIN TRAN;

BEGIN
INSERT INTO 
dbo.Shipment 
(ShipmentType, scaccode, ShipmentControl, countryLoading, PortLoading, ShipperName, 
 ShipperAddressOne, 
 ShipperCity, ShipperCountry, ShipperProvince, ShipperZip, ShipperPhone, ShipperEmail, ConsigneeName, 
 ConsigneeAddressOne, ConsigneeCity, ConsigneeCountry, ConsigneeState, ConsigneeZip,
 ConsigneePhone, ConsigneeEmail, ResponseCode, astrayExportDate, bondType, bondDestPort, 
 bondOnwardscac, bondCarrierIRS, bondNumber, bondTransferIRS, bondForeignPort, bondDepartureDate, 
 MexiPediNumber)
VALUES  
(@ShipmentType, @SCACCode, @SCNumber, @CountryLoading, @PortLoading, @ShipperName, @ShipperAddress, 
@ShipperCity, @ShipmentCountry, @ShipmentProvince,                  @ShipperZip, @ShipperPhone, 
@ShipperEmail, @ConsigneeName, @ConsigneeAddress, @ConsigneeCity, @ConsigneeCountry, @ConsigneeState, 
@ConsigneeZip,@ConsigneePhone, @ConsigneeEmail, @CustomControl, @DateShipmentLeft, @InbondEntryType, 
@InbondDestination, @OnwardCarrier, @BondedCarrier, @Inbond, @TransferCarrier, 
@ForeignPortDestination, @EstimatedDeparture, @MexiPediNumber)
SELECT @Id=@@IDENTITY
    SET @RetVal = 'true|Record Inserted Successfully|'+CAST(@Id AS VARCHAR)
END                                                 


 BEGIN  
 SELECT @ShipmentID=@Id
 INSERT INTO dbo.Ship_commodity(ShipmentID,[Description],Quantity,manifestUnitCode, 
 [weight],weightUnitCode,countryCode,customsShipmentValue,commodityCode,MarksNumbers)                         
 VALUES    
 (@ShipmentID, @Description, @Quantity, @QuantityUnit, @Weight, @WeightUnitCode, @Countrycode, 
 @Value,@HarmonizedCode,@MarksNumbers)
 SELECT @CMDId=@@IDENTITY       
                                        
    SET @RetVal = 'true|Record Inserted Successfully|'+CAST(@CMDId AS VARCHAR)
END

BEGIN

IF(ISNULL(@CmdHMCode1,'')<>'')
    INSERT INTO dbo.Commodity_Hazmat(CommodityID,HazmatCode,HazmatContact,HazmatComNum) 
                              VALUES(@CMDId,@CmdHMCode1,@CmdHMName1,@CmdHMPhone1)
IF(ISNULL(@CmdHMCode2,'')<>'')
    INSERT INTO dbo.Commodity_Hazmat(CommodityID,HazmatCode,HazmatContact,HazmatComNum) 
                              VALUES(@CMDId,@CmdHMCode2,@CmdHMName2,@CmdHMPhone2)
IF(ISNULL(@CmdHMCode3,'')<>'')
    INSERT INTO dbo.Commodity_Hazmat(CommodityID,HazmatCode,HazmatContact,HazmatComNum) 
                              VALUES(@CMDId,@CmdHMCode3,@CmdHMName3,@CmdHMPhone3)       
     END                       
COMMIT TRAN;                                            
END TRY                                                 
BEGIN CATCH                                         
    ROLLBACK TRAN;                              
    SET @RetVal='false|Technical Error-'+ERROR_MESSAGE()+'|'+ ERROR_PROCEDURE() + ' LineNo : ' + 
 CONVERT(VARCHAR, ERROR_LINE()) +'|'
END CATCH                                       
END
GO

Answer 1

You have a long transaction block that can fail in many different places. I might suggest that you put some auditing directly in the stored procedure.

In particular, you can add a print statement to the catch block to see if there are any other errors that are arising.

I would add a new variable, something like:

declare @auditLevel int = 0;

Then conditionally print to see where there is a problem:

if @auditLevel > 0
begin
    print 'Hey, I''m here';
end;