title: parametrized sql with multiple parameters
body: <p>I am missing something in my SQL query, could please someone advise how to do correct it? This is my SQL:</p>
  
  <pre><code>declare @numItems int;
  declare @value1 int;
  declare @value2 int;
  declare @currentValue int;
  declare @counter int;
  declare @qry nvarchar(max)
  
  set @value1 = 5;
  set @value2 = 10;
  
  set @numItems = 2
  set @counter=0
  
  WHILE (@counter &lt; @numItems)
  BEGIN
      set @qry = 'set @currentValue = @value' + cast(@counter+1 as nvarchar(max)) + ';'   
      exec sp_executesql @qry, N'@currentValue int OUTPUT', @currentValue OUTPUT
  
      print @currentValue
  
      set @counter = @counter + 1;
  END
  </code></pre>
  
  <p>What I want is that @currentValue parameter gets values of @value1 and @value2 and prints them.
  I'm not sure how to correctly declare parameters in the exec statement. I am using SQL Server 2005. Thanks for any help.</p>