Concatenate Strings to make column alias

I am trying to make a column alias that concatenates strings (one coming from a parameter).

I'm running the same query with different values on the parameter to generate my report to send out, and I want the parameter value included in the column headers/names so that it is clear to the end user (and to myself 30 seconds later) which parameter was used.

I'm trying something like this, but it won't execute

declare @fy int
set @fy = 2021

    [Section Name],
    [Revenue] as concat(str(@fy),' Earned Revenue'),

A complete toy example for trying is like this (less stuff, lets just make the param a string

declare @fy nvarchar(4)
set @fy = '2021'

    'asdf' as concat(@fy,' column')

I'm getting

Incorrect syntax near '@fy'.`


  • Names of database objects such as tables, columns, stored procedures, views, etc, can contain alphanumeric characters and must begin with an alphabet or an underscore, not with digit.

    We need to use dynamic query to achieve dynamic column alias name.

    DECLARE @fy INT = 2021; 
    DECLARE @sql NVARCHAR(1000) = 'SELECT 1234 AS EarnedRevenue'+CAST(@fy AS NVARCHAR(4));
    EXEC (@sql)

    enter image description here