1

I have a table with column_a, column_b, column_c.

I would like to pass a list of objects to a stored procedure, each object representing a row in the table.

Meaning:

CREATE PROCEDURE [dbo].[InsertMultipleObject] 
    (@Objects List<MyEntity>)

MyEntity stores values for column_a, column_b, column_c.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user4675862
  • 35
  • 1
  • 6

1 Answers1

2

You'll have to use Table-Valued parameters. A link here: https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine

In your case you need to create a custom type in database (I declared column types as INT, as an example)

CREATE TYPE YourTypeName AS TABLE (
    column_a INT
  , column_b INT
  , column_c INT);

One that's done, your procedure can be created as follows:

CREATE PROCEDURE [dbo].[InsertMultipleObject] (
    @Objects YourTypeName READONLY
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM @Objects;
END;

DECLARE @Objects YourTypeName;

INSERT INTO @Objects (column_a, column_b, column_c)
VALUES (1, 1, 1)
    , (2, 2, 2)
    , (3, 3, 3);

EXECUTE dbo.InsertMultipleObject @Objects = @Objects;

So this has taken a single parameter which contained multiple rows/columns as a parameter and simply made a SELECT * from it.

Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
  • OK, but i need to pass it to the stored procedure as a list of those objects, the way you showed allows to pass only one object. – user4675862 Jul 16 '17 at 09:39
  • This answer is correct. It allows you to pass a table with multiple records, because you've created a user defined table type which is used as a parameter in the procedure. Each row in the passed table corresponds to what you refer to as an "object". – dybzon Jul 16 '17 at 09:43
  • @user4675862 What Rasmus has said. Couldn't explain it any better. If you needed a single object. You simply can pass a parameter for each of your columns, that's it :) – Evaldas Buinauskas Jul 16 '17 at 09:45
  • I think I miss something here. Indeed I need multiple records per table but the list should be of multiple tables. – user4675862 Jul 16 '17 at 09:52
  • Actually, try checking this out. I think it's exactly what you're looking for: https://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure – Evaldas Buinauskas Jul 16 '17 at 10:03
  • @user4675862 Updated. If this is not what you're looking for. Clarify your question. – Evaldas Buinauskas Jul 16 '17 at 10:11