TSQL, передающий массив 2 значений в хранимую процедуру

Question

Я использую SQL Server 2012 и C#.

Представьте, что у вас есть что-то похожее на корзину для покупок, и теперь нужно создать заказ из следующих предметов:

productA - 4 (qty)  
productB - 1 (qty) 
productC - 9 (qty)

В моем коде C# у меня есть список, который выглядит следующим образом:

id : "productA" , qty : "4"  
id : "productB" , qty : "1"  
id : "productV" , qty : "9"

Вопросы:

1. Как передать список из 2 значений хранимой процедуре?

2. Как я могу запустить хранимую процедуру 3 в то время как каждый цикл повторяется 4 раза, затем один раз, а затем 9 раз, чтобы физически создать одну запись x запроса?

Примечание: в моем случае у меня нет QTY столбец в таблице, мне нужно специально создать одну запись х элемент в заказе.

Answer 1

1. Это можно сделать с помощью параметра "Значение таблицы" в SQL.

Sql Authority

MSDN

2. Вы можете сделать это, передав TVP как @table формат

   declare @table table(product varchar(10), qty int)
   
   insert into @table
      select 'product1', 4 union
      select 'product2', 2
   
   ;WITH cte AS (
      SELECT product, qty FROM @table
      UNION ALL
      SELECT product, qty-1 FROM cte WHERE qty > 1
   )
   SELECT t.product, t.qty 
   FROM cte c
   JOIN @table t ON c.product = t.product
   ORDER BY 1
   

Ссылка на CTE: создание дублирующихся записей для данной строки таблицы

Answer 2

Чтобы передать таблицу в хранимую процедуру, используйте параметр табличного значения.

Сначала создайте тип:

CREATE TYPE [dbo].[ProductsTableType] AS TABLE(
    [ID] [varchar](50) NOT NULL,
    [qty] [int] NOT NULL
)

Затем используйте этот тип в хранимой процедуре. @ParamProducts является таблицей и может использоваться во всех запросах, где таблица может быть использована.

CREATE PROCEDURE [dbo].[AddProducts]
    @ParamProducts ProductsTableType READONLY
AS
BEGIN
    ...
END

Чтобы фактически вставить необходимое количество строк, я бы использовал таблицу чисел, http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

В моей базе данных есть таблица Numbers с колонной Number который содержит числа от 1 до 100 000. После того, как у вас есть такой стол, тривиально получить набор, который вам нужен.

DECLARE @T TABLE (ID varchar(50), qty int);

INSERT INTO @T (ID, qty) VALUES ('productA', 4);
INSERT INTO @T (ID, qty) VALUES ('productB', 1);
INSERT INTO @T (ID, qty) VALUES ('productV', 9);

SELECT * 
FROM
    @T AS Products
    INNER JOIN dbo.Numbers ON Products.qty >= dbo.Numbers.Number
;

Набор результатов

ID          qty Number
productA    4   1
productA    4   2
productA    4   3
productA    4   4
productB    1   1
productV    9   1
productV    9   2
productV    9   3
productV    9   4
productV    9   5
productV    9   6
productV    9   7
productV    9   8
productV    9   9

Это пример. В вашем случае вы бы это SELECT внутри INSERT INTO YourFinalTable,