title: Allocate multiple parameters based on similar queries
body: <p>In a stored procedure, I need to populate a number of parameters. The value of each parameter is based on a very similar query, identical except for the right hand operand of the where clause:</p>
  <pre><code>SELECT @param_a = f1 FROM t1 WHERE f2 = 'foo' -- this can be re-written as IN ('foo') so all queries are identical in form
  SELECT @param_b = f1 FROM t1 WHERE f2 IN ('bar', 'baz)'
  SELECT @param_c = f1 FROM t1 WHERE f2 IN ('qux', 'quux')
  [...]
  </code></pre>
  <p>The actual query is much longer (multiple joined tables instead of just t1), which makes this seem like a very cumbersome way to do this. I have been thinking to store the parameters with their respective arguments in a temp table like this:</p>
  <pre><code>param      operand
  ---------- --------
  @param_a   'foo'
  @param_b   'bar', 'baz'
  @param_c   'qux', 'quux'
  [...]
  </code></pre>
  <p>And then iterate over the temp table with a cursor and each time dynamically build and execute my query, which would look something like this:</p>
  <pre><code>DECLARE my_cursor CURSOR FOR
  SELECT param, operand FROM #my_temp_table
  
  OPEN my_cursor
  FETCH NEXT FROM my_cursor INTO @param, @operand
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @sttmt = 'SELECT @param = f1 FROM t1 WHERE f2 IN (@operand)'
    SELECT @sttmt_params = '@operand varchar(max),
                            @paramOUT varchar(max) OUTPUT'
    EXEC sp_executesql @sttmt, 
                       @sttmt_params, 
                       @operand = @operand, 
                       @paramOUT = @param[a/b/c/..] -- Here is where it goes wrong
  
    FETCH NEXT FROM my_cursor INTO @param, @operand
  END
  CLOSE my_cursor
  DEALLOCATE my_cursor
  </code></pre>
  <p>As illustrated, the problem is that I still have to hard-code my parameter name, so that doesn't bring me any further. But maybe I am not even thinking in the right direction and there is another way of doing this? Or do I really need to copy-paste that same query for each of my parameters?</p>