Looping table valued parameter values passed as parameter to stored procedure for checking a value

Question

I have a stored procedure that receives a TVP as input. Now, I need to check the received data for a particular ID in a primary key column. If it exists, then I just need to update the table using those new values for other column (sent via TVP). Else, do an insert.

How to do it?

CREATE PROCEDURE ABC 
    @tvp MyTable READONLY
AS
    IF EXISTS (SELECT 1 FROM MYTAB WHERE ID= @tvp.ID)
         DO update
    ELSE
         Create

Just wondering the if exists loop I did is correct. I reckon its wrong as it will only check for first value and then update. What about other values? How should I loop through this?

Answer 1

Looping/CURSOR is the weapon of last resort, always search for solution that is SET based, not ROW based.

You should use MERGE which is designed for this type of operation:

MERGE table_name AS TGT
USING (SELECT * FROM @tvp) AS SRC
   ON TGT.id = SRC.ID
WHEN MATCHED THEN 
    UPDATE SET col = SRC.col
WHEN NOT MATCHED THEN
   INSERT (col_name, col_name2, ...)
   VALUES (SRC.col_name1, SRC.col_name2, ...)

If you don't like MERGE use INSERT/UPDATE:

UPDATE table_name
SET col = tv.col,
    ...
FROM table_name AS tab
JOIN @tvp AS tv
   ON tab.id = tv.id


INSERT INTO table_name(col1, col2, ...)
SELECT tv.col1, tv.col2, ...
FROM table_name AS tab
RIGHT JOIN @tvp AS tv
  ON tab.id = tv.id
WHERE tab.id IS NULL