dynamic alias in sql server -

i want query field different alias in stored procedure

select count(empcode)  countemp+@para 

result shoud be

countemp1

45

countemp2

54

countemp1

76

query loop in c# code:

select count(empcode) = @something countemp+@para  

approach without dynamic sql:

--i create temp table demonstration declare @some_table table (     int,     empcode int )  insert @some_table (something, empcode) values (1, 10),(1, 22),(1, 12),(2, 12),(2, 30),(3, 65),(3, 15),(3, 11),(3, 5)  --declare parameter want search declare @param int = 1  --query --in cte select need based on parameter ;with cte ( select  'countemp'+cast(@param nvarchar(10)) something,         cast(count(empcode) nvarchar(10)) empcodecount,         row_number() on (order ) rn @some_table = @param group ) --and here comes union select result (     select something,rn     cte     union     select empcodecount ,rn     cte     ) t order rn, desc 

output:

result ------------------ countemp1 3  (2 row(s) affected)