TSQL passing 2 values array to stored procedure

Question

I'm using SQL Server 2012 and C#.

Imagine have something similar to a shopping cart and now need to create an order with the following items:

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

In my C# code I have a list that looks like this:

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

Questions:

How can I pass the list of 2 values to the stored procedure?
How can I have the stored procedure run 3 while loops each one running 4 times, then once then 9 times in order to physically create one record x request?

Note: In my case I don't have a QTY column in the table, I need to specifically create one record x item on the order.

Have a look at [table-valued parameters](https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx) for passing the list and [recursive CTEs](https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx) or using a numbers table to generate multiple rows. FWIW, most designs would include a `Quantity` column rather than trying to group rows whenever a count is needed. — HABO, Mar 04 '15 at 02:46
I agree about the QTY but in this case it does not really work. I'll check the Table-Valued param.... — SF Developer, Mar 04 '15 at 02:51

score 2 · Accepted Answer · edited May 23 '17 at 10:09

You can done this by Table Value Parameter in SQL.

Sql Authority

MSDN

You can done this by passing TVP as @table format

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

Reference for the CTE : Creating duplicate records for a given table row

score 1 · Answer 2 · edited Nov 13 '18 at 23:13

To pass a table into the stored procedure use table-valued parameter.

At first create a type:

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

Then use this type in the stored procedure. The @ParamProducts is a table and can be used in all queries where a table can be used.

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

To actually insert required number of rows I would use a table of numbers , http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

In my database I have a table called Numbers with a column Number that contains numbers from 1 to 100,000. Once you have such table it is trivial to get the set that you need.

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
;

Result set

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

This is an example. In your case you would have this SELECT inside INSERT INTO YourFinalTable.

TSQL passing 2 values array to stored procedure

2 Answers2