How to use parameters in CTE
<p>This question is about SQL Server / TSQL.</p>

<p>I use the following technique in Postgres:</p>

<pre><code>with i(username, name) as (values (?, ?))
insert into access_log
  (username
  ,name
  ,uid
  )
  values (select i.username
                ,i.name
                ,tab.uid
            from i
            join listofusers tab
              on tab.username  = i.username
          )
</code></pre>

<p>That is: I create a CTE at the top of my insert/select with the parameters I am passing in. This solves a handful of problems:</p>

<ol>
<li>It allows using the parameter in locations that would otherwise not be allowed.</li>
<li>It allows using the same parameter multiple times without specifying it twice on the input.</li>
<li>It allows me to rearrange the ? in the select without going to the code to change the order of the parameters being specified.</li>
<li>It allows a quick way to look at the list of what is being passed in to get the order correct.</li>
</ol>

<p>My question is: how can I do this in T-SQL <strong>with a single statement</strong>?</p>

<p>Note that I am not asking for a multiple statement solution. I am only going to accept an answer that can run in a single execute statement in an jdbc/odbc program and does not create temporary tables.</p>

<p>I won't flag answers that are multiple statement solutions because they might be helpful to others.</p>