Табличный параметр с изменениями первичных ключей

Question

Я только начинаю использовать параметр табличного значения в SQL Server 2008 для обновления таблицы. По сути, я создаю определяемое пользователем отображение типа таблицы на существующую таблицу столбец за столбцом и использую его как параметр с табличным значением, передавая данные на сервер для удаления, обновления и вставки. в большинстве случаев это работает нормально. однако для таблицы с составными первичными ключами, когда часть ключей была изменена, этот подход потерпит неудачу, так как tvp содержит только один набор значений ключа (либо текущий, либо исходный). Мой вопрос: как лучше всего справляться с такими случаями? мне нужно добавить второй набор столбцов первичных ключей в определении типа таблицы?

Упрощенный пример: Таблица:

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);

Теперь вопрос, как использовать табличный параметр (tvp_Prices), чтобы сохранить изменения на сервере?

Answer 1

Параметры табличных значений используются для передачи нескольких строк данных через параметры, например, в хранимую процедуру. В вашем случае вариант использования может быть следующим:

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]

Результат

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

---

Пример ОБНОВЛЕНИЯ

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]

Выход

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