How do I set up a stored procedure that does these 2 transactions as an atomic operation?

Question

I have a table like

CREATE TABLE Partners 
( 
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    first_name NVARCHAR(50) NOT NULL,
    last_name NVARCHAR(50) NOT NULL,
    org_name NVARCHAR(50) NOT NULL,
    email NVARCHAR(254) NOT NULL, -- 254 is optimal length according to http://stackoverflow.com/questions/1199190/what-is-the-optimal-length-for-an-email-address-in-a-database
    region_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (region_id) REFERENCES Regions(id)
);

and a stored procedure

CREATE PROCEDURE AddPartner (@first_name NVARCHAR(50),
                             @last_name NVARCHAR(50),
                             @org_name NVARCHAR(50),
                             @email NVARCHAR(254),
                             @region_id INT,
                             @new_guid UNIQUEIDENTIFIER OUTPUT)
AS
BEGIN
      DECLARE @T TABLE ( OutputGuid UNIQUEIDENTIFIER )

      INSERT INTO Partners  (first_name,last_name,org_name,email,region_id)
      OUTPUT INSERTED.id INTO @T
      VALUES (@first_name,@last_name,@org_name,@email,@region_id)

      SELECT @new_guid = OutputGUID FROM @T
  END

for inserting a partner and outputting the newly generated id of the partner.

Now I just learned that I need a one-to-many relationship between Partners and another basic table I have called Regions. So I made an intersection table

-- Create intersection table that associates partners with regions
CREATE TABLE Destinations 
(
    id INT IDENTITY(1,1),
    partner_id UNIQUEIDENTIFIER, 
    region_id INT,
    FOREIGN KEY (partner_id) REFERENCES Partners(id),
    FOREIGN KEY (region_id) REFERENCES Regions(id)
);

When a partner is added in the user interface, that's the time when the regions associated with the partner are added to the Destinations table. I want that to be an atomic operation (if there's a problem adding the a Destination, rollback the insertion of the partner) and I've never done something like this.

Question 1: how do I insert a list of region ids into the stored procedure? I understand how I could do it with multiple calls to the stored procedure, but I'm supposing that there's some way to do it with a single call and I don't know how.

Question 2: Can you give me an idea of how I would write this query?

Question 3: Can you give me an idea of how I would modify my C# code

Guid? pid = null;

// Insert partner to database
using (SqlCommand cmd = new SqlCommand("AddPartner", this._Conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@first_name", P.FirstName);
    cmd.Parameters.AddWithValue("@last_name", P.LastName);
    cmd.Parameters.AddWithValue("@org_name", P.OrgName);
    cmd.Parameters.AddWithValue("@email", P.Email);
    cmd.Parameters.AddWithValue("@region_id", P.RegionId);

    SqlParameter output = new SqlParameter("@new_guid", SqlDbType.UniqueIdentifier) {  Direction = ParameterDirection.Output };
    cmd.Parameters.Add(output);

    this._Conn.Open();
    cmd.ExecuteNonQuery();

    pid = (Guid?)output.Value;
    this._Conn.Close();
}

to call this new query? P is defined by

public class PartnerInit
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string OrgName { get; set; }
    public string Email { get; set; }
    public int RegionId { get; set; }
    public List<int> Destinations { get; set; }
}

and I'm not sure how to get that Destinations field into a stored procedure.

There are numerous questions here. The easiest path to get Destinations as a single parameter is with a table valued parameter. https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx — Sean Lange, Feb 15 '16 at 19:33
An SO link for using table-valued parameters in C#/SQL. Just be sure to use the second answer (NOT the accepted answer): http://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code — Tom H, Feb 15 '16 at 19:59
99% of the time you dont need table valued parameters. change your model. — Uğur Gümüşhan, Feb 15 '16 at 20:22

score 3 · Accepted Answer · edited May 23 '17 at 10:28

This question shows how to pass a table-valued parameter to a stored procedure: How to pass table value parameters to stored procedure from .net code

Once you have that working, your stored procedure will look something like:

CREATE PROCEDURE dbo.AddPartner
    @first_name NVARCHAR(50),
    @last_name NVARCHAR(50),
    @org_name NVARCHAR(50),
    @email NVARCHAR(254),
    @region_ids dbo.DestinationsTableType,
    @new_guid UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

        INSERT INTO dbo.Partners...

        INSERT INTO dbo.Destinations (partner_id, region_id)
        SELECT @new_guid, region_id
        FROM @region_ids

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION
    END CATCH
END

Of course, you'll need to add appropriate error handling, etc.

Note that you have to check `XACT_STATE` value instead of `TRANCOUNT` inside `catch` block. — Ivan Starostin, Feb 16 '16 at 13:22
No, you don't. If you have specific requirements such that you might need to use a `COMMIT` inside of your `CATCH` block then you might have need of `XACT_STATE`, but it's certainly not required. — Tom H, Feb 16 '16 at 20:22

How do I set up a stored procedure that does these 2 transactions as an atomic operation?

1 Answers1