Preserving non-inserted data for a record

Question

I am experimenting with table valued parameters (TVP) and how to use them from C# code. One specific aspect of TVP is giving me trouble: When passing a list of data to the Stored Procedure and wanting to update that list after the data has come back from the database.

Here is a sample class that would be contained within a list:

public class Phone
{
    int _phoneID;
    string _phoneNumber;
    Guid _correlationID;
}

When passing this list to a stored procedure that uses TVP, how would I be able to insert the _phoneID and the _phoneNumber into the database and, after the insert, update the list of Phones with the _phoneID based on the _correlationID?

Here is a sample table, type, and stored procedure:

Create Table PhoneTable
(
    PhoneID int identity(1,1),
    PhoneNumber varchar(20)
)
GO

Create Type PhoneType As Table
(
    PhoneNumber varchar(20),
    CorrelationID uniqueidentifier
)
GO

Create Procedure usp_Phone_Insert
    @Input PhoneType ReadOnly
AS
    Declare @Output Table (PhoneID int, PhoneNumber varchar(20))

    Insert Phone (PhoneNumber)
    Output
        inserted.PhoneID,
        inserted.PhoneNumber
    Into @Output
    Select 
        PhoneNumber
    From
        @Input

    Select PhoneID, PhoneNumber From @Output
GO

The reason for a correlation id is for the ability to track the objects all the way from the application to the database and back.

I think you'll need to add `CorrelationId` to the `Phone` table if you want the `output` clause to provide matching `PhoneID`/`CorrelationId` values. Alternatively you can match them after the `insert` by using an `inner join` on `PhoneNumber` _if the telephone numbers are always unique_. — HABO, Apr 10 '13 at 03:27

scottmgerstl · Accepted Answer · 2013-04-10T15:44:06.087

0

Based on the comment under my question about inner joins, I came to this solution and it looks to work very well. This approach will only work if the data that is being inserted is unique in the set.

Modified table, type and stored procedure to accomplish my needs for a correlation ID:

Create Table PhoneTable
(
    PhoneID int identity(1,1),
    PhoneNumber varchar(20)
)
GO

Create Type PhoneType As Table
(
    PhoneNumber varchar(20),
    CorrelationID bigint
)
GO

Create Procedure usp_Phone_Insert
    @Input PhoneType ReadOnly
AS
    Declare @Output Table (
        PhoneID int, 
        PhoneNumber varchar(20),
        CorrelationID bigint)

    Insert Phone (PhoneNumber)
    Output
        inserted.PhoneID,
        inserted.PhoneNumber
    Into @Output
    Select 
        PhoneNumber
    From
        @Input

    Select
        O.PhoneID,
        O.PhoneNumber,
        I.CorrelationID
    From
        @Output O
        Inner Join @Input I on I.PhoneNumber = O.PhoneNumber
GO

edited Apr 10 '13 at 15:44

answered Apr 10 '13 at 15:34

scottmgerstl

765
8
18

Based on the telephone numbers being unique, you could omit the `output` and simply join `@Input` with `Phone`. The performance will depend on whether `Phone` is indexed by `PhoneNumber` and the typical size of `@Input`. – HABO Apr 10 '13 at 16:45
I agree that you could and, with this given use case it may make sense. However, using the output clause gives you the benefit of joining on less data (the database table could contain millions of records and there could be many more columns to join on). Also, this approach relies on data being unique but, with using the output table, the data only has to be unique within the dataset passed in. One final consideration is that reading information out of the output table allows you the efficiency of not revisiting the table. – scottmgerstl Apr 10 '13 at 18:07

Preserving non-inserted data for a record

1 Answers1