title: How to pass array to stored procedure in order to insert some records consecutively
body: <p>As everybody knows there is no array in SQL Server, there are some workarounds to achieve that somehow, like table-valued parameters (TVP) and user-defined data type (UDT) or even a parameter as comma-separated values (CSV).</p>
  
  <p>There are also some examples on the internet that show how to use it, but most of them apply <code>SELECT</code> statement and use the parameter directly just like this:</p>
  
  <pre><code>CREATE PROCEDURE dbo.GetOrderList1
      (@OrderList VARCHAR(500))
  AS
  BEGIN
      SET NOCOUNT ON
  
      DECLARE @SQL VARCHAR(600)
  
      SET @SQL =  'SELECT OrderID, CustomerID, EmployeeID, OrderDate
                   FROM dbo.Orders
                   WHERE OrderID IN (' + @OrderList + ')'
  
      EXEC(@SQL)  
  END
  </code></pre>
  
  <p>But I need to use an <code>INSERT</code> statement and use every single part of parameter one by one.</p>
  
  <p>In my case I send a message to multiple participants by SignalR and I need to save it in data base too. Imagine I have a <code>MessageId</code> and I want to save it this way:</p>
  
  <pre><code>ID   |    MessageId   | ReceiverId
  -----+----------------+-----------
       |                |
  </code></pre>
  
  <p>I need my stored procedure to get just one <code>MessageId</code> say 100 and multiple <code>ReceiverId</code>s say (1001, 1002, 1003, 1004, 1005) and insert them in related table one by one, something like this:</p>
  
  <pre><code>EXEC sp_AddToMessageReceivers 100, (1001, 1002, 1003, 1004, 1005)
  
  CREATE PROCEDURE sp_AddToMessageReceivers
      @messageId INT, 
      @ReceiversIds VARCHAR(500)
  AS
  BEGIN
      DECLARE @i INT = 0
  
      WHILE @i &lt; @ReceiversIds.length
      BEGIN
          INSERT INTO [dbo].[MessageReceiver] (MessageId, ReceiverIds)
          VALUES (@MessageId, @ReceiversIds[i])
  
          SET @i = @i + 1
      END
  END
  </code></pre>
  
  <p>I know how to get the table valued parameter in stored procedure but I don't know how to work with them one by one in a while loop.</p>
  
  <p>I really appropriate it if you offer a solution for this problem.</p>