title: Unable to insert the records in the tables in SQL server
body: <p>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<br />
  id from Ship_commodity table=commodity id of Commodity_Hazmat table</p>
  <pre><code>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,'')&lt;&gt;'')
      INSERT INTO dbo.Commodity_Hazmat(CommodityID,HazmatCode,HazmatContact,HazmatComNum) 
                                VALUES(@CMDId,@CmdHMCode1,@CmdHMName1,@CmdHMPhone1)
  IF(ISNULL(@CmdHMCode2,'')&lt;&gt;'')
      INSERT INTO dbo.Commodity_Hazmat(CommodityID,HazmatCode,HazmatContact,HazmatComNum) 
                                VALUES(@CMDId,@CmdHMCode2,@CmdHMName2,@CmdHMPhone2)
  IF(ISNULL(@CmdHMCode3,'')&lt;&gt;'')
      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
  </code></pre>
  <p><a href="https://i.stack.imgur.com/yCGql.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yCGql.png" alt="image for executing the sp" /></a></p>