Inserting DataTable & individual parameters into a table using stored procedure

Question

I'm trying to update/insert a SQL table using a stored procedure. Its inputs are a DataTable and other individual parameters.

EmployeeDetails table:

ID |  Name | Address    |  Operation  |  Salary
---+-------+------------+-------------+------------
1  | Jeff  | Boston, MA |  Marketing  |  95000.00
2  | Cody  | Denver, CO |  Sales      |  91000.00 

Syntax for user-defined table type (DataTable):

CREATE TYPE EmpType AS TABLE 
(
    ID INT, 
    Name VARCHAR(3000), 
    Address VARCHAR(8000), 
    Operation SMALLINT
)

Procedure for the operation:

ALTER PROCEDURE spEmpDetails
    @Salary Decimal(10,2),
    @Details EmpType READONLY
AS
BEGIN
    UPDATE e 
    SET e.Name = d.Name, 
        e.Address = d.Address 
    FROM EmployeeDetails e, @Details d 
    WHERE d.ID = e.ID
            
   --For inserting the new records in the table
   INSERT INTO EmployeeDetails(ID, Name, Address) 
       SELECT ID, Name, Address 
       FROM @Details;
END

This procedure spEmpDetails gets its inputs as individual parameter @Salary and a DataTable @Details. Using these inputs, I'm trying to update/unsert the EmployeeDetails table. But, I failed to join these inputs together in the update/insert statement. In the above code, I'm only using the @Details DataTable data to update the EmployeeDetails table and I'm missing the @Salary to update in the table.

I'm looking for some suggestions on how to do it. Any suggestion will be greatly appreciated.

Answer 1

I think you're looking for something like this. By setting XACT_ABORT ON when there are 2 DML statements within a block then both will rollback completely if an exception is thrown. To ensure only new records are inserted an OUTPUT clause was added to the UPDATE statement in order to identify the ID's affected. The INSERT statement excludes ID's which were UPDATE'ed.

This situation is a little different from Aaron Bertrand's excellent answer. In that case there was only a single row being upserted and Aaron wisely checks to see if the UPDATE affected a row (by checking @@rowcount) before allowing the INSERT to happen. In this case the UDT could contain many rows so both UPDATE's and INSERT's are possible.

ALTER PROCEDURE spEmpDetails
    @Salary Decimal(10,2),
    @Details EmpType READONLY
AS
set nocount on;
set xact_abort on;
--set transaction isolation level serializable; /* please look into */

begin transaction
begin try
    declare @e          table(ID        int unique not null);

    UPDATE e 
    SET e.Name = d.Name, 
        e.Address = d.Address,
        e.Salary = @Salary
    output inserted.ID into @e
    FROM EmployeeDetails e,
         join @Details d on e.ID=d.ID;

   --For inserting the new records in the table
    INSERT INTO EmployeeDetails(ID, Name, Address, Operation, Salary) 
    SELECT ID, Name, Address, Operation, @Salary 
    FROM @Details d
    where not exists(select 1
                     from EmployeeDetails e
                     where d.ID=e.ID);

    commit transaction;
end try
begin catch
    /* logging / raiserror / throw */

    rollback transaction;
end catch
go

Answer 2

...but the input data table also gets one record at a time...

That's a dangerous assumption, even if you control the data table being sent to the stored procedure now. One day in the future you might be replaced, or someone else might want to use this stored procedure - and since the procedure itself have no built in protection against having multiple records in the data table - it's just a bug waiting to happen.

If you only need one record to be passed into the stored procedure, don't use a table valued parameter to begin with - instead, make all the parameters scalar.
Not only will it be safer, it would also convey the intent of the stored procedure better, and therefor make it easier to maintain.

If you want the stored procedure to be able to handle multiple records, add a salary column to the table valued parameter, and remove the @salary scalar parameter.

---

Having said that, there are a other problems in your stored procedure:

• There's no where clause in the insert...select statement - meaning you'll either insert all the records in the table valued parameter or fail with a unique constraint violation.
• You're using an implicit join in your update statement. It might not be a big problem when you only use inner join with two tables, but explicit joins made it to SQL-92 with good reasons - since they provide better readability and more importantly, better compilation checks. For more information, read Aaron Bertrand's Bad Habits to Kick : Using old-style JOINs

So, how to properly write an "upsert" procedure? Well, Aaron have written about that as well - right here in StackOverflow.

---

However, there are valid use-cases where you do want to combine inputs from both a table valued parameter and scalar variables - and the way you do that is very simple.

For an update:

UPDATE target
SET Column1 = source.Column1,
    Column2 = source.Column2,
    Column3 = @ScalarVariable
FROM TargetTable As target
JOIN @TVP As source
    ON target.Id = source.Id -- or whatever join condition

And for an insert:

INSERT INTO TargetTable(Column1, Column2, Column3) 
SELECT Column1, Column2, @ScalarVariable
FROM @TVP