-1

I have two tables Portfolio and Trades. Once the portfolio data has been inserted, using that Id, I insert the trades details into the Trades table.

I will get multiple portfolio in a single request, for example, if I get two portfolio P1 and P2, I need to insert two records into the Portfolio table, and the corresponding trades details into the Trades table once for every portfolio.

How to pass this entire data in a single stored procedure call in order to maintain the transaction?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vijay VSTO
  • 133
  • 1
  • 4
  • 15
  • 2
    Please add the details: Tables, data, expected results and what you have done so far – James Z Jun 21 '15 at 17:39
  • The portfolio table is having the columns ID, PortfolioName, CreatedBy and CreatedOn and the Trades table is having the columns PortfolioID (reference from Portfolio table ID), Ticker, No of Shares, Action. The data is P1, Vijay, 21-Jun-2015 – Vijay VSTO Jun 21 '15 at 18:01
  • The portfolio table is having the columns ID, PortfolioName, CreatedBy and CreatedOn and the Trades table is having the columns PortfolioID (reference from Portfolio table ID), Ticker, No of Shares, Action. Portfolio record 1: P1, Vijay, 21-Jun-2015 Trades for records 1: 1, MSFT, 50, Buy ; 1, IBM, 75, Sell. Portfolio Record 2: P2, Suresh, 21-Jun-2015 Trades for Records 2: 2, YAHOO, 30, Update; 2, Goog, 40, Buy. Currently, I am passing the two data table to the stored procedure one for Portfolio and one for Trades and calling the stored procedure for every portfolio. – Vijay VSTO Jun 21 '15 at 18:08
  • Is it possible to pass the entire portfolio and trades data to the stored procedure and iterate every portfolio and manipulate those records in the sql server itself without calling the stored procedure multiple time for every portfolio. – Vijay VSTO Jun 21 '15 at 18:08
  • Depending on your client, you might be able to use table valued parameters to pass the data to the stored procedure. – Zohar Peled Jun 21 '15 at 18:18
  • Why not enable transactions, call `begin transaction` and `commit` around the Stored Procedure calls? – Stavr00 Jun 21 '15 at 18:25
  • Is there any way to pass the entire data in a single call to stored procedure. – Vijay VSTO Jun 22 '15 at 07:30

1 Answers1

2

Starting with sql server 2008, you can use table valued parameters to pass a table structured data in a single call to a stored procedure.
However, Not every client supports this. It's easy to use with .Net, but I doubt it's possible with VBScript, for instance.

In order to use a table valued parameter you must first create a user defined table type:

CREATE TYPE UDT_Portfolio As Table
(
    Portfolio_Id int,
    Portfolio_Name varchar(10),
    .....
)

in your case, since you want to pass 2 tables, you need 2 types:

CREATE TYPE UDT_Trades As Table
(
    Trade_Id int,
    Trade_Portfolio_Id int,
    Trade_Name varchar(10),
    ....
)

Now, to use them in a stored procedure is very easy:

CREATE PROCEDURE stp_InsertPortfoliosAndTrades
(
     @Portfolio UDT_Portfolio readonly, -- Note: Readonly is a must
     @Trade UDT_Trades readonly
)
AS

-- stored procedure logic here

Note #1:: Table valued parameters are readonly. meaning you can't use update, insert or delete statements on them. However, you can declare a table valued variable inside a stored procedure and use insert, update, and deletes on that.

Note #2: If your portfolio id is an identity column, then when inserting the data to the portfolio table you will need to use an output clause to get the new id values.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Currently, I am doing in the way you have mentioned. My requirement is to process multiple portfolio at the same time. I am calling the stored procedure once for every portfolio. Is there any way to pass all the portfolios in a single call. – Vijay VSTO Jun 22 '15 at 17:41
  • If you are using a table valued parameter you can fill it with how many rows you want... what is the problem? – Zohar Peled Jun 22 '15 at 17:43
  • How will you maintain the relationship in the table valued parameter between the portfolio and trades table, if I pass the multiple portfolio and multiple trades for every portfolio. Hope my question is clear. – Vijay VSTO Jun 23 '15 at 12:13
  • Look [here](http://dba.stackexchange.com/questions/73635/insert-into-select-into-multiple-related-tables-using-insert-identity) and [there](http://stackoverflow.com/questions/3712678/how-can-i-insert-data-into-two-tables-simultaneously-in-sql-server) – Zohar Peled Jun 23 '15 at 13:56
  • Thanks for pointing me in the right direction. Thanks again :) – Vijay VSTO Jun 24 '15 at 18:16