title: Pass an array into stored procedure and insert those values SQL Server
body: <p>I have to insert these values into a table:</p>
  
  <pre><code>ID   Col1   col2  date
  1      10    5    getdate()
  2      20    5    getdate()
  3      30    5    getdate()
  </code></pre>
  
  <p>Column 1 comes in a string '10,20,30' and I am using function splintint to parse and get data</p>
  
  <pre><code>CREATE FUNCTION dbo.SplitInts
  (
     @List      VARCHAR(MAX),
     @Delimiter VARCHAR(255)
  )
  RETURNS TABLE
  AS
    RETURN ( SELECT Item = CONVERT(INT, Item) FROM
        ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
          FROM ( SELECT [XML] = CONVERT(XML, '&lt;i&gt;'
          + REPLACE(@List, @Delimiter, '&lt;/i&gt;&lt;i&gt;') + '&lt;/i&gt;').query('.')
            ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
        WHERE Item IS NOT NULL
    );
  GO
  </code></pre>
  
  <p>And in the stored procedure</p>
  
  <pre><code>SELECT @lenList = Count(Item) FROM dbo.SplitInts(@Col1List, ','); 
  IF @lenList &gt; 0
  
          WHILE @i &lt;= @lenList
          BEGIN 
              SELECT @col1 = CONVERT(INT,Item) FROM dbo.SplitInts(@Col1List, ','); 
  
  
              INSERT
              INTO [dbo].[Table1]
              (
                  [Col1],
                  [Col2],
                  [CreatedOn],
              )
              VALUES
              (
                  @col1,
                  @col2,
                  getdate()
              )
  
              SET @i = @i+1
          END
  </code></pre>
  
  <p>But the issue is I see only last row gets inserted every time i.e., 30, 5 here. What am I doing wrong here? Any help is appreciated.</p>