SQL Server: drop column @variable not working

Question

I am using Microsoft SQL Server and it seems that I cannot get around this problem.

I have a table in which I will have dynamic and static columns.

Static columns are the name of the product, type of product, and dynamic data is some production data from current months.

At the beginning of each month, I have to abandon the dynamic columns in the last month and add a new month to the end of the table

My solution was to store the column name in a local variable and then add it to the alter statement. But this does not work, it continues to give me an error:

Msg 102, Level 15, State 1, Line 18
Invalid syntax near '@month_b'

Now I will add queries

declare @month_t char(15) declare @month_b char(15) declare @sql char(30) set @month_b = (SELECT top 1 name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ct_test') AND name != 'TTNR' AND name != 'Family' AND name like '%B%' ORDER BY name ASC) set @month_t = (SELECT top 1 name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ct_test') AND name != 'TTNR' AND name != 'Family' AND name like '%T%' ORDER BY name ASC) alter table ct_test drop column @month_b 

I can not find a solution to this issue, you can help me.

Thank you in advance

Answer 1

You need to use Dynamic Query

 Declare @sql nvarchar(max) set @sql = 'alter table ct_test drop column '+ @month_b Exec sp_executesql @sql set @sql = 'alter table ct_test drop column '+ @month_t Exec sp_executesql @sql