SQL Server stored procedure summing one field with parameter input from C#

Stored procedure has multiple functions.

Step One: I need to retrieve an int from a table, SUM it with an @int variable that is user entered. Then store that value back into the same column it was retrieved from. I.E. We have one, we order one, add one because we now have two because that item is already in inventory.

Here’s what I have so far:

BEGIN
SET NOCOUNT ON;

DECLARE @TotalOnHand INT;     

SELECT  (
    Select Sum(TotalOnHand)
    FROM (Values (InvTotalQtyOnHand), (@InvTotalQtyOnHand)) as TSum(TotalOnHand)
)
FROM InventoryMaster_tbl
INNER JOIN PurchaseOrderItems_tbl PartManufPartNum On PartManufPartNum = InvManPartNumber
WHERE POItemsID = @POItemsID
UPDATE [InventoryMaster_tbl]
SET InvTotalQtyOnHand = @TotalOnHand

END 

When ran I see the InvTotalQtyOnHand now = 2 (there was one in the column and user entered 1 = 2 in the qry results window

Error I get:

Cannot insert the value NULL into column ‘InvTotalQtyOnHand’, table ‘TrackLinq.dbo.InventoryMaster_tbl’; column does not allow nulls. UPDATE fails.

Obviously InvTotalQtyOnHand becomes Null since @TotalOnHand = null

First post here so be nice. I’ve researched this site for years… I know how it can be… 🙂 I hope I was concise enough for you all.

Thanks in advance.

Answer

You didn’t assign value to @TotalOnHand so the value is null. You can do it in a following way.

BEGIN
SET NOCOUNT ON;

DECLARE @TotalOnHand INT;  --newly declared, not assigned. NULL   

SELECT  @TotalOnHand = (
    Select Sum(TotalOnHand)
    FROM (Values (InvTotalQtyOnHand), (@InvTotalQtyOnHand)) as TSum(TotalOnHand)
)
FROM InventoryMaster_tbl
INNER JOIN PurchaseOrderItems_tbl PartManufPartNum On PartManufPartNum = InvManPartNumber
WHERE POItemsID = @POItemsID

UPDATE [InventoryMaster_tbl]
SET InvTotalQtyOnHand = @TotalOnHand -- now assigned
--you forget filter. All rows will be updated
where <some condition>

END 

In fact you don’t need first select statement. You can do update directly.

UPDATE [InventoryMaster_tbl]
SET InvTotalQtyOnHand = InvTotalQtyOnHand + @InvTotalQtyOnHand --comes from SP parameters
FROM InventoryMaster_tbl
INNER JOIN PurchaseOrderItems_tbl PartManufPartNum On PartManufPartNum = InvManPartNumber
WHERE POItemsID = @POItemsID --comes from SP parameters