1

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.

1

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).

1

T-SQL can't be used like C/C++ and there are no such things as Macros in T-SQL that would work like this. If you want dynamic column names and usage, then I would suggest dynamic SQL.

Or, if you want a real adventure, you could write sqlcmd scripts that could do this. Of course, they would need to be executed using sqlcmd, or in SSMS, but it would technically allow you to do this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.