title: How to fetch values in an Excel file and repeat a stored procedure execution more than once?
body: <p>I'm using SQL Server and I have the following T-SQL script:</p>
  <pre><code>USE [MyDatabase]
  GO
  
  DECLARE @var1 NCHAR(2)
  DECLARE @var2 DATE
  DECLARE @var3 NVARCHAR(100)
  DECLARE @var4 BIT
  
  SET @var1 = val1
  SET @var2 = val2
  SET @var3 = val3
  SET @var4 = val4
  
  BEGIN TRANSACTION
  
  SELECT a.*
  FROM [MyDataBase].[dbo].[MyTable1] a (NOLOCK)
  INNER JOIN [MyDataBase].[dbo].[MyTable2] b (NOLOCK) ON b.id2 = a.id1
  WHERE b.Col1 = @var1
  ORDER BY a.id1
  
  EXEC [MyDataBase].[obj].[MyStoredProc] 
          @param1=@var1, @param2=@var3, @param3=somevalue, @param4= @var2
  
  SELECT a.*
  FROM [MyDataBase].[dbo].[MyTable1] a (NOLOCK)
  INNER JOIN [MyDataBase].[dbo].[MyTable2] b (NOLOCK) ON b.id2 = a.id1
  WHERE b.Col1 = @var1
  ORDER BY a.id1
  
  ROLLBACK TRANSACTION
  --COMMIT TRANSACTION
  </code></pre>
  <p>Then I have an Excel file with one column and more than 600 lines (each line is an INT).</p>
  <p>The goal of the above script is to repeat in the transaction the same stored procedure <code>[MyStoredProc]</code> by varying the parameter <code>@param3</code>. The parameter <code>@param3</code> retrieves values from the Excel file.
  This procedure is good when I have a very limited values to put in @param3. For that I can just repeat the same EXEC instruction as possible as I have value like this:</p>
  <pre><code>EXEC [MyDataBase].[obj].[MyStoredProc] 
          @param1=@var1, @param2=@var3, @param3=somevalue1, @param4= @var2
  EXEC [MyDataBase].[obj].[MyStoredProc] 
          @param1=@var1, @param2=@var3, @param3=somevalue2, @param4= @var2
  EXEC [MyDataBase].[obj].[MyStoredProc] 
          @param1=@var1, @param2=@var3, @param3=somevalue3, @param4= @var2
  ...
  </code></pre>
  <p>Unfortunately, in my Excel file I have more than 600 values.</p>
  <p>So my questions are:</p>
  <ol>
  <li>How to use my Excel file to be able to fetch value in it ?</li>
  <li>How can I use a one line <code>EXEC</code> instruction (something like a loop while) for the more than 600 values.</li>
  </ol>
  <p>I'm using Microsoft SQL Server Management Studio</p>