SQL Server: having issues inserting/updating values with if statements in a stored procedure

Everything before the first check BSW alarm executes correctly. Anything being evaluated by the else/if statements doesn't seem to run the insert statements on the prod library table and I'm not sure why.

I have to pass a lot of variables this way because I'm sourcing the data not from a database. I have tested the queries separately to the stored procedure (insert statements) and they work fine, so it's not a null restriction issue or anything like that.

This is the code:

ALTER PROCEDURE [dbo].[Truck_Offload]
@Contract int,
@Source int,
@Product int,
@Truck_Ticket int,
@Truck_Volume bigint,
@Flow_weighted_Dens real,
@Avg_meter_temp real,
@Avg_meter_press real,
@fast_loop_dens real,
@offload_date_year int,
@offload_date_month int,
@offload_date_day int,
@offload_date_hour int,
@offload_date_min int,
@BSW real,
@BSW_alarm bit,
@BOL bigint,
@library_ID bigint OUTPUT
AS
BEGIN
DECLARE @offload_datetime date;
DECLARE @temp_result int;
--DECLARE @contract_to_use int;
DECLARE @source_to_use int;
DECLARE @product_string nchar(10);
DECLARE @density_min real;
DECLARE @density_max real;
DECLARE @density_temp real;
DECLARE @Offload_ID bigint;
DECLARE @request_ID bigint;
DECLARE @library_ID_temp int;
DECLARE @offload_total int;
DECLARE @offload_max int;
DECLARE @lib_ID_temp bigint;
SET NOCOUNT ON;
--If sample is to be purged, library_ID will be 0
SELECT @library_ID = 0;
-- Insert statements for procedure here
SELECT @offload_datetime = DATETIMEFROMPARTS(@offload_date_year, @offload_date_month,@offload_date_day, @offload_date_hour, @offload_date_min, 0, 0);
---find if contract exists
SELECT @temp_result = Contract 
FROM Offload_Contracts
WHERE Contract = @Contract;
--if it doesn't exist, add contract
IF @temp_result = NULL 
BEGIN
SELECT @temp_result = eOne_ID 
FROM Src_Locations
WHERE eOne_ID = @Source;
IF @temp_result = NULL
BEGIN
INSERT INTO Src_Locations (eOne_ID) 
VALUES (@Source);
SELECT @source_to_use = SCOPE_IDENTITY();
--The name of a new Source Location will be unknown and need to be modified in a GUI.
END
ELSE
BEGIN
SELECT @source_to_use = @temp_result;
END
--SELECT PRODUCT TO USE FOR NEW CONTRACT
SELECT @product_string = CASE @Product
WHEN 1 THEN  'C3+'
WHEN 2 THEN 'C5+'
ELSE 'C5+'
END;
--CREATE NEW CONTRACT
INSERT INTO Offload_Contracts (Contract, Offload_ID, Product, Source_ID)
VALUES (@Contract, 1, @product_string, @source_to_use);
END
-- Use Contract, establish new Offload_Registry Entry. First Insert Truck_Ticket.
INSERT INTO truck_tickets (Offload_Volume, UOM, Flow_weighted_Dens, meter_average_temp, truck_average_press, fast_loop_dens, offload_date, BSW, BSW_alarm, sys_ticket)
VALUES (@Truck_Volume, 'm3', @Flow_weighted_Dens, @Avg_meter_temp, @Avg_meter_press, @fast_loop_dens, @offload_datetime, @BSW, 
, @Truck_Ticket);
--Now that Truck Ticket has been created, create offload Registry
INSERT INTO OffLoad_Registry (Offload_Contract, BOL, truck_ticket)
SELECT @Contract, @BOL, SCOPE_IDENTITY(); --SCOPE_IDENTITY returns the truck_ticket_number
-- Have the Offload ID handy for later
SELECT @Offload_ID = SCOPE_IDENTITY();
--NOW we need to do logic to determine whether to keep or purge the sample
--First Check if there was a BSW alarm
IF @BSW_alarm = 1 AND @Product = 2
BEGIN
--create new library record
INSERT INTO Prod_Library (Offload_ID, Lab_Status) 
VALUES (@Offload_ID, 'Waiting');
SELECT @library_ID = SCOPE_IDENTITY();
UPDATE Offload_Contracts 
SET offload_total = 1
WHERE Contract = @Contract;
END
ELSE
BEGIN
--check if there's been any retain requests made against the contract fed to the stored procedure
SELECT @request_ID = Request_ID 
FROM Retain_Requests 
WHERE Contract = @Contract;
--if there's a retain request, automatically create a library record
IF @request_ID <> NULL
BEGIN
INSERT INTO Prod_Library (Offload_ID, Lab_Status) 
VALUES (@Offload_ID, 'Waiting');
SELECT @library_ID = SCOPE_IDENTITY();
--update contract
UPDATE Offload_Contracts 
SET offload_total = 1
WHERE Contract = @Contract;
END
ELSE
BEGIN
--check if there's a density + origin + contract against the library
SELECT @lib_ID_temp = P.Lib_ID
FROM dbo.Prod_Library P
LEFT JOIN Offload_Registry OfR ON P.Offload_ID = Ofr.Offload_ID
LEFT JOIN Offload_Contracts OC ON Ofr.Offload_Contract = OC.Contract				
WHERE Ofr.Offload_Contract = @Contract 
AND (Lab_Status = 'Waiting' Or Lab_Status = 'Approved');
IF @lib_ID_temp = NULL
BEGIN
INSERT INTO Prod_Library (Offload_ID, Lab_Status) 
VALUES (@Offload_ID, 'Waiting');
SELECT @library_ID = SCOPE_IDENTITY();
END
--contract will be updated for offload total if the operator follows the recommendation
ELSE
--check retention criteria
BEGIN
--get retention criteria
SELECT @offload_total = OC.offload_total, @density_temp = TT.Flow_weighted_Dens, @density_min = Density_Min, @density_max = Density_Max, @offload_max = Truck_offloads
FROM Prod_Library AS PL
INNER JOIN Offload_Registry OFR ON PL.Offload_ID = OFR.Offload_ID
INNER JOIN truck_tickets TT ON OFR.truck_ticket = TT.Ticket
INNER JOIN Offload_Contracts OC ON OC.Contract = OFR.Offload_Contract
INNER JOIN Products Prod ON Prod.Product = OC.Product
WHERE PL.Lib_ID = @lib_ID_temp;
IF @offload_total >= @offload_max OR @Flow_weighted_Dens < @density_temp - @density_min OR @Flow_weighted_Dens > @density_temp + @density_max
-- Retain sample if this is true
BEGIN
INSERT INTO Prod_Library (Offload_ID, Lab_Status) 
VALUES (@Offload_ID, 'Waiting');
SELECT @library_ID = SCOPE_IDENTITY();
UPDATE Offload_Contracts 
SET offload_total = 1
WHERE Contract = @Contract;
END
ELSE
BEGIN
-- update contracts offload total, sample will be purged
UPDATE Offload_Contracts 
SET offload_total += 1
WHERE Contract = @Contract;
END
END
END
END
END	

Calling code was like this, and the return value was always 0.

EXECUTE [dbo].[Truck_Offload] 
@Contract = @Contract
,@Source = @Source
,@Product = @Product
,@Truck_Ticket = @Truck_Ticket
,@Truck_Volume = @Truck_Volume
,@Flow_weighted_Dens = @Flow_weighted_Dens
,@Avg_meter_temp = @Avg_meter_temp
,@Avg_meter_press = @Avg_meter_press
,@fast_loop_dens = @fast_loop_dens
,@offload_date_year = @offload_date_year
,@offload_date_month = @offload_date_month
,@offload_date_day = @offload_date_day
,@offload_date_hour = @offload_date_hour
,@offload_date_min = @offload_date_min
,@BSW = @BSW
,@BSW_alarm = @BSW_alarm
,@BOL = @BOL
,@library_ID = @library_ID OUTPUT
SELECT @library_ID;

--This Question has been solved, see comments about NULL statements. Thank you Sean.

The issue is that nothing ever equals NULL. It will always evaluate to false. The solution is to use IS NULL or IS NOT NULL.

You have a couple locations in the code you posted where you would need to change this to work the way you want it to.

So IF @temp_result = NULL would need to be rewritten as:

IF @temp_result IS NULL

and IF @request_ID <> NULL would become:

IF @request_ID IS NOT NULL

You have a couple other places that match the same pattern and the same solution will correct those as well.