Middle of Stored Procedure doesn't work sometimes

I have a stored procedure that makes several inserts into different tables. Everything inserts fine except for in the middle of the sproc. The Insert into tblHeatTransaction area only works about 90% of the time. There is no pattern to the missed inserts and the data is good. If I run the procedure with the exact same data it will insert fine.
I figured I would get this out to you guys and see what you think before falling back to look deeper at the C# code.
This is an ASP.NET C# project with SQL SERVER 2012
Take a look:
ALTER PROCEDURE [dbo].[CompleteBOLTransfer]
--(@Bolnum nvarchar(25), @HeatID int)
(@ShipDate datetime,@CustomerNumber int,@ShipToNumber int,@CarrierNumber int,@BillToNumber int,@Notes nvarchar(150),@SessionID nvarchar(50))

AS

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
   -- DECLARE @BLNum as nvarchar(25)
DECLARE @Control as int
DECLARE @HeatID as int
DECLARE @CoilNumber as nvarchar(25)

DECLARE @FinishedSize as nvarchar(15)
DECLARE @Grade as nvarchar(15)
DECLARE @HeatNumber as nvarchar(20)
DECLARE @NetWeight as int
DECLARE @TareWeight as int
DECLARE @NoOfCoils as int
DECLARE @BOLNumber as int

DECLARE @SplitCoils as int
DECLARE @Description as nvarchar(50)
DECLARE @CustomerPONumber as nvarchar(20)
DECLARE @TrxType as nvarchar(1)
DECLARE @TrxDate as datetime
DECLARE @CoilsShipped as int
DECLARE @BLId as int
DECLARE @VendorName as nvarchar(25)
DECLARE @VendorPO as nvarchar(15)
DECLARE @RodSize as nvarchar(10)
DECLARE @VendorNumber as int
DECLARE @DetailID as int
DECLARE @NoOfCarriers as int

DECLARE @TransferFromCustomer AS int
DECLARE @GroupKey AS int
SET @GroupKey = 0
DECLARE @LastControl AS int
SET @LastControl = 0
DECLARE @LastHeatNumber AS nvarchar(25)
SET @LastHeatNumber = ''   
DECLARE @LastHeatID AS int
SET @LastHeatID = 0

DECLARE @TotalCarriers int = 0

DECLARE @err int,
        @value int

SELECT @err = @@error
IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'


DECLARE @TransSave bit = 0
 

--DECLARE @DateReceived as smalldatetime
DECLARE @ID as int

-- Create the BOL Header Record

INSERT INTO tblBOLHeader
(
ShipDate,
CustomerNumber,
ShipToNumber,
CarrierNumber,
BillToNumber,
Notes
)
VALUES
(
@ShipDate,
@CustomerNumber,
@ShipToNumber,
@CarrierNumber,
@BillToNumber,
@Notes
)

SELECT @BLID =@@IDENTITY
FROM tblBOLHeader

-- Insert records from tblBOLTransfer into BOLDetail


    DECLARE DetailsCursor CURSOR FAST_FORWARD FOR
    
    SELECT a.[Control],a.HeatNumber,a.Grade,a.FinishedSize,a.NetWeight,a.NoOfCoils,a.VendorName,a.VendorPO,a.RodSize,a.VendorNumber,a.SplitCoils,a.TareWeight,a.HeatID,a.NoOfCarriers,a.CustomerPONumber,b.[Description]
    FROM tblBOLTransfer a
    JOIN tblOrderDetail b ON b.[Control] = a.[Control]
    WHERE a.SessionID = @SessionID
    ORDER BY a.[Control],a.HeatNumber,a.HeatID
  

    OPEN DetailsCursor

	 
    FETCH NEXT FROM DetailsCursor INTO @Control,@HeatNumber,@Grade,@FinishedSize,@NetWeight,@NoOfCoils,@VendorName,@VendorPO,@RodSize,@VendorNumber,@SplitCoils,@TareWeight,@HeatID,@NoOfCarriers,@CustomerPONumber,@Description
    
    --SELECT @Description = tblOrderDetail.Description --,@CustomerPONumber = tblOrderHeader.CustomerPONumber  
    -- FROM tblOrderDetail INNER JOIN tblOrderHeader ON tblOrderHeader.OrderNumber = tblOrderDetail.OrderNumber
    -- WHERE tblOrderDetail.Control = @Control
    
    

	

   

WHILE @@FETCH_STATUS = 0
BEGIN

IF (@LastControl != @Control) OR (@LastHeatNumber <> @HeatNumber) OR (@LastHeatID <> @HeatID)
  BEGIN
     SET @GroupKey = @GroupKey + 1
     SET @LastControl = @Control
     SET @LastHeatNumber = @HeatNumber
     SET @LastHeatID = @HeatID
  END
   
   
SET @TotalCarriers = @TotalCarriers + @NoOfCarriers   
    
    --PRINT 'Coil: ' +@CoilNumber
INSERT INTO tblBOLDetail
(
BOLNumber,
Control,
HeatID,
NoOfCoils,
[Description],
CustomerPONumber,
NetWeight,
TareWeight,
SplitCoils,
NoOfCarriers,
HeatNumber,
Grade,
FinishedSize,
GroupKey

)

VALUES
(
@BLID,
@Control,
@HeatID,
@NoOfCoils,
@Description,
@CustomerPONumber,
@NetWeight,
@TareWeight,
@SplitCoils,
@NoOfCarriers,
@HeatNumber,
@Grade,
@FinishedSize,
@GroupKey
)

SELECT @DetailID =@@IDENTITY
FROM tblBOLDetail

--INSERT INTO HeatTransaction -- (this is where it doesn't work sometimes)

SELECT @TransferFromCustomer = TransferFromCustomer FROM tblHeatTransaction WHERE Control = @Control AND TrxType = 'A'
SET @TransferFromCustomer = COALESCE( @TransferFromCustomer,0)
--SELECT @err = @@error
--F @err <> 0
 --  PRINT '@err is ' + ltrim(str(@err)) + '.'

 


INSERT INTO tblHeatTransaction
(
HeatID,
CustomerNumber,
TrxType,
TrxDate,
BOLNumber,
CoilsShipped,
LbsShipped,
Control,
BLId,
TransferFromCustomer,
HeatNumber,
Grade,
MachineID

)
VALUES
(
@HeatID,
@CustomerNumber,
'S',
@ShipDate,
@BLId,
@NoOfCoils,
@NetWeight,
@Control,
@DetailID,
@TransferFromCustomer,
@HeatNumber,
@Grade,
777
)

--I added the following to retry the insert if it didn't add the record....this never fired because it never wrote a "99" --transaction into the table

SET @TransSave = 0
SELECT @TransSave = COUNT(*) FROM tblHeatTransaction b  WHERE 
  b.HeatID = @HeatID AND b.BOLNumber = @BLId AND b.Control = @Control

IF @TransSave = 0
	 BEGIN
			INSERT INTO tblHeatTransaction
		(
		HeatID,
		CustomerNumber,
		TrxType,
		TrxDate,
		BOLNumber,
		CoilsShipped,
		LbsShipped,
		Control,
		BLId,
		TransferFromCustomer,
		HeatNumber,
		Grade,
		MachineID

		)
		VALUES
		(
		@HeatID,
		@CustomerNumber,
		'S',
		@ShipDate,
		@BLId,
		@NoOfCoils,
		@NetWeight,
		@Control,
		@DetailID,
		@TransferFromCustomer,
		@HeatNumber,
		@Grade,
		99
		)
	END
	


FETCH NEXT FROM DetailsCursor INTO @Control,@HeatNumber,@Grade,@FinishedSize,@NetWeight,@NoOfCoils,@VendorName,@VendorPO,@RodSize,@VendorNumber,@SplitCoils,@TareWeight,@HeatID,@NoOfCarriers,@CustomerPONumber,@Description
SELECT @err = @@error
IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'
	

END -- OF CURSOR LOOP


--iNSERT INTO WIRE CARRIER

INSERT INTO tblWireCarriers
(
BOLNumber,
ShipToCustNumber,
CarriersShipped,
TrxDate

)
VALUES
(
@BLID,
@ShipToNumber,
@TotalCarriers,
@ShipDate

)


-- Update CoilLog

UPDATE t
SET Status = 'WIP'
FROM tblCoilID t
JOIN tblCoilLog l ON l.CoilID = t.CoilID
WHERE l.BOLNumber = @BLId
SELECT @err = @@error
IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'


UPDATE t
SET BOLNumber = '',TranType = ''
FROM tblCoilLog t
WHERE t.BOLNumber = @BLId
SELECT @err = @@error
IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'



UPDATE t
SET BOLNumber = @BLId,TranType = 'S'
FROM tblCoilLog t
INNER JOIN tblPickList p ON t.CoilNumber = p.CoilNumber 
WHERE t.FinishedGood = 'True' AND p.SessionID = @SessionID
SELECT @err = @@error
IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'


-- Update CoilID





UPDATE t
SET STATUS = 'SHIPPED'
FROM tblCoilID t
INNER JOIN tblPickList p ON t.CoilNumber = CASE WHEN ( CHARINDEX('/',p.CoilNumber) > 0 ) THEN LEFT(p.CoilNumber,CHARINDEX('/',p.CoilNumber)-1) ELSE p.CoilNumber END
WHERE SessionID = @SessionID
SELECT @err = @@error
IF @err <> 0
   PRINT '@err is ' + ltrim(str(@err)) + '.'


CLOSE DetailsCursor
DEALLOCATE DetailsCursor


DELETE FROM tblPickList WHERE SessionID = @SessionID
DELETE FROM tblBOLTransfer WHERE SessionID = @SessionID

Open in new window

ITMikeKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
in your code you seem to check the errors, but only "print" and nowhere to rollback and abort on errors... hence part of the code may work, and the rest not, and by running the procedure from c#, you won't see any errors coming through

check out "RAISERROR" function : https://msdn.microsoft.com/en-us/library/ms178592.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
How do you know if there was missing inserts?
0
ITMikeKAuthor Commented:
I can see there are missing BOLNumbers in  tblHeatTransaction table. There is no pattern that we can tell. This weekend we are putting code into place that will message us with the situation of why the insert did not happen.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, the messages should be a good solution to help you find something that justifies the behavior. By the code it should do the inserts without any issue.
0
ITMikeKAuthor Commented:
The issue stemmed from an update procedure with the editing process, it would blow the record away!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.