Implement more conditions on upsert (user defined table types) in SQL Server?

Question

I've a table named 'VendorItemPricing' in my database. I'll insert/update data into this table periodically using Data Table, in other words bulk insert/update operation I'll perform.

Below is my stored procedure to perform this operation, and it works good.

Assume this is my table VendorItemPricing:

ItemPartNumber  VendorName  VendorPrice UpdatedDate                 ObsoleteItem    IsLocked
Z0PD            Apple       1177        2015-05-27 11:11:14.700     0               0
C1GM            Apple       181.25      2015-05-27 11:11:14.700     0               1

Whenever I send a datatable to my stored procedure it'll check the condition.

Assume this is my input:

ItemPartNumber => Z0PD
VendorName => Apple

Now, it check with the condition.

tableVendor.ItemPartNumber = 'Z0PD' and tableVendor.VendorName = 'Apple' and tableVendor.IsLocked = 0

The first item will get updated since it satisfied the given condition.

Now assume this is my input:

ItemPartNumber => ZWEPD
VendorName => Apple

Now, it again check with the condition.

tableVendor.ItemPartNumber = 'ZWEPD' and tableVendor.VendorName = 'Apple' and tableVendor.IsLocked = 0

The new row will be inserted since the input data isn't matching with the condition. This's also great.

But in this type of input,

ItemPartNumber => C1GM
VendorName => Apple

When it check with the condition.

tableVendor.ItemPartNumber = 'C1GM' and tableVendor.VendorName = 'Apple' and tableVendor.IsLocked = 0

The condition is now false, and the query is inserting the new row. :(

It shouldn't insert the row, since there's a data with this part number. If Item with IsLocked = 1, it should not get neither updated nor inserted.

I hope I explained my situation clearly. Can anyone help me in fixing this error?

This is my stored procedure.

ALTER PROCEDURE [dbo].[WP_InsertUpdateVendorItemPrices]
    @inputTable InsertUpdateVendorPrices READONLY
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO VendorItemPricing tableVendor
    USING @inputTable tableTemp
    ON tableVendor.ItemPartNumber = tableTemp.ItemPartNumber and tableVendor.VendorName = tableTemp.VendorName and tableVendor.IsLocked = 0 
    WHEN MATCHED THEN
    UPDATE SET tableVendor.VendorPrice = tableTemp.VendorPrice, tableVendor.UpdatedDate = GETUTCDATE(), tableVendor.ObsoleteItem = 0
    WHEN NOT MATCHED THEN
    INSERT VALUES(tableTemp.ItemPartNumber, tableTemp.VendorName, tableTemp.VendorPrice, GETUTCDATE(), 0, 0); 
END

Answer 1

Myself got an answer. Thanks everyone.

This is the latest updated working stored procedure.

ALTER PROCEDURE [dbo].[WP_InsertUpdateVendorItemPrices]
      @inputTable InsertUpdateVendorPrices READONLY
AS
BEGIN
    SET NOCOUNT ON;

    MERGE INTO VendorItemPricing tableVendor
    USING @inputTable tableTemp
    ON tableVendor.ItemPartNumber = tableTemp.ItemPartNumber and tableVendor.VendorName = tableTemp.VendorName
    WHEN MATCHED THEN
    UPDATE SET 
        tableVendor.VendorPrice = CASE WHEN tableVendor.IsLocked = 0 THEN tableTemp.VendorPrice ELSE tableVendor.VendorPrice END,
        tableVendor.UpdatedDate = CASE WHEN tableVendor.IsLocked = 0 THEN GETUTCDATE() ELSE tableVendor.UpdatedDate END,
        tableVendor.ObsoleteItem = CASE WHEN tableVendor.IsLocked = 0 THEN 0 ELSE tableVendor.ObsoleteItem END
    WHEN NOT MATCHED THEN
    INSERT VALUES(tableTemp.ItemPartNumber, tableTemp.[VendorName], tableTemp.[VendorPrice], GETUTCDATE(), 0, 0); 
END