How to define macros in SQL server? I want to define a macro in my SQL script and later based on my conditional query I want to create a new variable name which will be 'X'+macro name. 

For example, say I define a macro 'month' which has the current month of the system. 

DECLARE @month AS VARCHAR(20);
SET @month = DATENAME(MONTH, GETDATE()), 3)


Upon execution, say it returns 'Nov'.
Now further in the script, I want to execute a query which based on CASE/WHEN will create a new variable which will be called something like 'Product_Nov'. When I use the below mentioned query my output is a new column named 'Product_@month' whereas I wanted 'Product_Nov'.

CASE
WHEN x ='Y' then 1
ELSE 0
END AS Product_@month


Any help/advice/suggestions much appreciated.
I sort of simulate macros using dynamic SQL, but it's pretty clunky since this isn't what T-SQL was designed for.

DECLARE @sql nvarchar(max) = N'SELECT foo_$alias$ = 1;';

DECLARE @alias sysname = N'bar';

SET @sql = REPLACE(@sql, N'$alias$', @alias);

EXEC sys.sp_executesql @sql;


Output:

foo_bar
-------
      1


My concern would be that the consuming application is able to handle all of the potential column names coming back from the database (I assume your real example is more complex, because the application certainly has the ability to know the current month).