MS SQL Server: Assign value to variable and reuse the var within the query -

i doing follwoing e.g.:

select name1, name2, left(name1,2) + '_' + left(name2,2), id, id + left(name1,2) + '_' + left(name2,2)  person 

so left(name1,2) + '_' + left(name2,2) used more 1 times. there way can better. have queries have call same function chain 10 - 15 times.

other example:

i queries need values sub-query

e.g.

select name, (select some_table x=y) , age person 

in cases need value returned sub-query different column also.

e.g.

select name, (select some_table x=y) , age, left( (select some_table x=y),2) test  person 

there must easier way...right?

you can use outer apply re-use code, this:

select p.name, x.something, p.age, left(x.something,2) test person p outer apply (     select some_table s s.x=p.y ) x