Table valued parameter with primary keys changes

Question

I am just starting to use table valued parameter in sql server 2008 to update table. basically, I create user defined table type mapping to an existing table column by column, and use that as table valued parameter, passing data to the server for delete, update and insert. in most case, that works fine. however, for table with composite primary keys, when part of the keys got changed, this approach would fail, as the tvp only contains one set of the key values (either current or original). My question is what is the best way to handle such cases? do I have to add a second sets of primary keys columns in the table type definition?

A simplified example: Table:

CREATE TABLE [dbo].[Prices](
    [ID] [int] NOT NULL PRIMARY KEY,
    [Date] [smalldatetime] NOT NULL PRIMARY KEY,
    [Value] [float] NOT NULL,
CONSTRAINT [PK_Prices] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [Date] ASC
)
)

Table Type
CREATE TYPE [dbo].[tvp_Prices] AS TABLE(
    [ID] [int] NOT NULL,
    [Date] [smalldatetime] NOT NULL,
    [Value] [float] NOT NULL
)

Add some data to the table
Insert Into  [dbo].[Prices] Values (1, '1/1/2015', 1.2)
Insert Into  [dbo].[Prices] Values (1, '1/2/2015', 1.3)
Insert Into  [dbo].[Prices] Values (2, '1/1/2015', 1.4)
Insert Into  [dbo].[Prices] Values (2, '1/2/2015', 1.5)

in c#, load the Prices to DataTable pricesTable,

update some value:
pricesTable.Rows[1]["Date"] = new DateTime(2015, 1, 3);

Now the question how to use the table valued parameter (tvp_Prices) to save the changes back to the server?

not clear what you mean by "such cases"? Please, add more details and show what you already have done. — Andrey Morozov, Jan 05 '15 at 15:57
Can you add an example of where the conflicts occur? I'm not at all clear about what your problem is. — Code Different, Jan 05 '15 at 16:01

Andrey Morozov · Accepted Answer · 2015-01-05T19:04:01.960

Table value parameters is used to pass multiple rows of data through parameters to, for example, stored procedure. In your case the use case could be the next:

CREATE TYPE [dbo].[tvp_Prices] AS TABLE(
    [ID] [int] NOT NULL,
    [Date] [smalldatetime] NOT NULL,
    [Value] [float] NOT NULL
);

CREATE TABLE [dbo].[Prices](
    [ID] [int] NOT NULL PRIMARY KEY,
    [Date] [smalldatetime] NOT NULL PRIMARY KEY,
    [Value] [float] NOT NULL,
    CONSTRAINT [PK_Prices] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC,
        [Date] ASC
    )
);

CREATE PROC dbo.use_Prices
(
    @prices [dbo].[tvp_Prices] READONLY
)
AS BEGIN
    INSERT INTO [dbo].[Prices] ([ID], [Date], [Value])
    SELECT * FROM @prices;
END


-- usage ---------------------------------

DECLARE
    @prices [dbo].[tvp_Prices];

Insert Into  @prices Values (1, '1/1/2015', 1.2)
Insert Into  @prices Values (1, '1/2/2015', 1.3)
Insert Into  @prices Values (2, '1/1/2015', 1.4)
Insert Into  @prices Values (2, '1/2/2015', 1.5)

SELECT * FROM [dbo].[Prices]

EXEC dbo.use_Prices @prices;

SELECT * FROM [dbo].[Prices]

Result

ID  Date    Value
-----------------------------

-

ID  Date                Value
-----------------------------
1   2015-01-01 00:00:00 1.2
1   2015-01-02 00:00:00 1.3
2   2015-01-01 00:00:00 1.4
2   2015-01-02 00:00:00 1.5

Example of UPDATE

CREATE PROC dbo.use_UpdatePrices
(
    @prices [dbo].[tvp_Prices] READONLY
)
AS BEGIN
    -- update values by the PK
    UPDATE p
    SET
        p.[Value] = p1.[Value]
    FROM [dbo].[Prices] p
    JOIN @prices p1 ON p1.ID = p.[ID]
    AND p1.[Date] = p.[Date];
END

Truncate table [dbo].[Prices]

Insert Into  [dbo].[Prices] Values (1, '1/1/2015', 1.2)
Insert Into  [dbo].[Prices] Values (1, '1/2/2015', 1.3)
Insert Into  [dbo].[Prices] Values (2, '1/1/2015', 1.4)
Insert Into  [dbo].[Prices] Values (2, '1/2/2015', 1.5)

-- usage -----------------------------------
DECLARE
@prices [dbo].[tvp_Prices];

-- values to update
Insert Into  @prices Values (1, '1/1/2015', 10.10) 
Insert Into  @prices Values (1, '1/2/2015', 11.11)

SELECT * FROM [dbo].[Prices]

EXEC dbo.use_UpdatePrices @prices;

SELECT * FROM [dbo].[Prices]

Output

ID  Date                Value
-----------------------------
1   2015-01-01 00:00:00 1.2
1   2015-01-02 00:00:00 1.3
2   2015-01-01 00:00:00 1.4
2   2015-01-02 00:00:00 1.5


ID  Date                Value
-----------------------------
1   2015-01-01 00:00:00 10.1
1   2015-01-02 00:00:00 11.11
2   2015-01-01 00:00:00 1.4
2   2015-01-02 00:00:00 1.5

Thanks. For insert, I understand this would work fine. however, what about update with part of the primary key changed as stated in my simplified example? — Qstonr, Jan 05 '15 at 18:12
Updating the part of primary key is a very bad idea. Don't update the primary keys EVER. It's absolutely useless idea because primary key is a unique identifier for each row in a table and if you change it - it would be like if you delete this row and inserts another. This "problem" is not related to the use of TVP at all — Andrey Morozov, Jan 05 '15 at 18:57
and for the future: http://www.codeproject.com/Articles/24340/Breaking-the-Law-Primary-Keys-in-SQL-Server — Andrey Morozov, Jan 05 '15 at 18:58
thanks. though the code you provided did not help me much, but your comment about updating part of primary keys is a bad idea did help me. I had a feeling this might not be the desired way, your comment convinced me to take a different approach. — Qstonr, Jan 06 '15 at 21:50

Table valued parameter with primary keys changes

1 Answers1

Example of UPDATE