sql server - How to use parameters in CTE -

this question sql server / tsql.

i use following technique in postgres:

with i(username, name) (values (?, ?)) insert access_log   (username   ,name   ,uid   )   values (select i.username                 ,i.name                 ,tab.uid                         join listofusers tab               on tab.username  = i.username           ) 

that is: create cte @ top of insert/select parameters passing in. solves handful of problems:

1. it allows using parameter in locations otherwise not allowed.
2. it allows using same parameter multiple times without specifying twice on input.
3. it allows me rearrange ? in select without going code change order of parameters being specified.
4. it allows quick way @ list of being passed in order correct.

my question is: how can in t-sql with single statement?

note not asking multiple statement solution. going accept answer can run in single execute statement in jdbc/odbc program , not create temporary tables.

i won't flag answers multiple statement solutions because might helpful others.

you can use parameters in row constructor in t-sql need select within cte , insert...select instead of insert...values:

with (     select *      (values (?, ?)) i(username, name)     ) insert access_ace_log_restype   (username   ,name   ,uid     )     select          i.username         ,i.name         ,tab.uid         join listofusers tab         on tab.username  = i.username; 

here examples without row constructor:

with (     select ? username, ? name     ) insert access_ace_log_restype   (username   ,name   ,uid     )     select          i.username         ,i.name         ,tab.uid         join listofusers tab         on tab.username  = i.username;  i(username, name) (     select ?, ?     ) insert access_ace_log_restype   (username   ,name   ,uid     )     select          i.username         ,i.name         ,tab.uid         join listofusers tab         on tab.username  = i.username;