CODEWITHSUNDEEP
sql-serverstored-proceduresinsert
reubenCanowski
reubenCanowski

Reputation: 262

Update record, if fail, insert record then update

I have a stored procedure that updates inventory items across several locations. What I'm mostly trying to do is allow for proper record keeping for inventory transfers between locations.

The problem I'm running into is that some of these locations don't have records for most types of inventory items, so when I try to update quantities ( -1 from start location, +1 to new location) there is no record to update.

Here is my stored procedure :

 CREATE  procedure dbo.Inv_Transfer (@p_hStock numeric,
 @p_sFProp varchar(20), @p_hTProp numeric,
 @p_dQuan numeric,  @p_Date datetime,
 @p_sUser1 varchar(1000),@p_sUser2 varchar(1000))
 as
 declare
    @v_FInvhMy numeric,
    @v_TInvhMy numeric,
    @v_sStockCode varchar(10),
    @v_hFProp numeric,
    @v_ibegin int,
    @v_iend int
 begin

     set @v_ibegin  = charindex('(', @p_sFProp  ) 
    if @v_ibegin <= 0 
    begin 
       raiserror('From property string not readable',16,1, @p_sFProp ) 
        return(0)
    end
    else
    begin
       set @v_iend  = charindex (')',@p_sFProp) 
       set @v_hFProp  = substring(@p_sFProp,@v_ibegin + 1,@v_iend - @v_ibegin -1) 
    end  

    select @v_sStockCode = sCode  from mm2stock where hMy = @p_hStock 
    if @@ERROR<> 0
    begin
        raiserror('Stock read failed ',16,1)
        return(0)
    end
     select @v_FInvhMy = hMy from mm2inventory where hStock = @p_hStock and hStoreProp = @v_hFprop 
    if @@ERROR<> 0
    begin
        raiserror('From inventory read failed ',16,1)
        return(0)
    end
     select @v_TInvhMy = hMy from mm2inventory where hstock = @p_hStock and hStoreProp = @p_hTProp 
    if @@ERROR<> 0
    begin
        raiserror('To inventory read failed ',16,1)
        return(0)
    end 
    update mm2inventory set iQtyonHand = iQtyonHand - @p_dquan where hmy = @v_FInvhMy
    if @@ERROR<> 0
    begin
        raiserror('Update from inventory failed ',16,1)
        return(0)
    end         
     update mm2inventory set iQtyonHand = iQtyonHand + @p_dquan where hmy = @v_TInvhMy
    if @@ERROR<> 0
    begin
        raiserror('Update to inventory failed ',16,1)
        return(0)
    end     
    else
    begin
        INSERT INTO mm2inventory (scode, hstock, hstoreprop, dcosteach, dbillprice, ireorderlevel, ireorderqty, iqtyonorder, iqtyonhand, suser1, suser2, suser3, suser4, snotes)
        SELECT (SELECT hmy + 1 where hmy in (select max(hmy) from mm2inventory)),@p_hStock,@p_hTProp,(SELECT dcosteach from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),(SELECT dbillprice from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),(SELECT ireorderlevel from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),(SELECT ireorderqty from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),0,0,'','','','','' FROM mm2inventory

        update mm2inventory set iQtyonHand = iQtyonHand + @p_dquan where hmy = @v_TInvhMy
    end  
     insert into mm2InvXfer( sStock,hinvfrom,hinvto,dquant,dtdate,sUser1,sUser2)
        values (@v_sStockCode,@v_FinvhMy, @v_TInvhMy, @p_dquan,
         isnull(@p_Date,getdate()), @p_sUser1, @p_sUser2) 
    if @@ERROR<> 0
    begin
        raiserror('Insert into inventoryxfer table  failed ',16,1)
        return(0)
    end
 end

Excerpt that I'm working on:

update mm2inventory set iQtyonHand = iQtyonHand - @p_dquan where hmy = @v_FInvhMy
    if @@ERROR<> 0
    begin
        raiserror('Update from inventory failed ',16,1)
        return(0)
    end         
     update mm2inventory set iQtyonHand = iQtyonHand + @p_dquan where hmy = @v_TInvhMy
    if @@ERROR<> 0
    begin
        raiserror('Update to inventory failed ',16,1)
        return(0)
    end     
    else
    begin
        INSERT INTO mm2inventory (scode, hstock, hstoreprop, dcosteach, dbillprice, ireorderlevel, ireorderqty, iqtyonorder, iqtyonhand, suser1, suser2, suser3, suser4, snotes)
        SELECT (SELECT hmy + 1 where hmy in (select max(hmy) from mm2inventory)),@p_hStock,@p_hTProp,(SELECT dcosteach from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),(SELECT dbillprice from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),(SELECT ireorderlevel from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),(SELECT ireorderqty from mm2inventory where hstock = @p_hStock and hstoreprop = @v_hFProp),0,0,'','','','','' FROM mm2inventory

        update mm2inventory set iQtyonHand = iQtyonHand + @p_dquan where hmy = @v_TInvhMy
    end

So, as you can see above, I'm trying to update (which works fine if records exist for the inventory item for both storage locations) but if there's an error then I want to Insert my new row for that particular inventory item and then update it with the new quantity value, but I'm doing something incorrectly.

Can anyone tell me what I'm doing wrong? Thanks

Upvotes: 3

Views: 1831

Answers (1)

coge.soft
coge.soft

Reputation: 1674

Here's a simple sample of what you can do and you don't need nearly as much error handling (I'm assuming there is a unique constraint on product_code):

--Make sure the inventory record exists:

INSERT INTO inventory (product_code, product_name)
SELECT product_code, product_name FROM source s
WHERE NOT EXISTS (
    SELECT product_code 
    FROM inventory i
    WHERE i.product_code = s.product_code)

--Updates the inventory record because you now know it exists:

UPDATE i
SET i.qty = i.qty + s.qty --obviously change sign where appropriate
FROM inventory i
JOIN source s ON s.product_code = i.product_code

What this will do is attempt to INSERT every time, but it naturally filters out anything that already exists. If there is nothing to INSERT, nothing happens. Then you can easily pursue the UPDATE.

Upvotes: 1

Related Questions