I'm using Microsoft SQL Server and it seems that I cannot get around this issue.
I have a table on which I will have some dynamic and static columns.
Static columns would be name of product, type of product and the dynamic data would be some production data from ongoing months.
At the every beginning of the month I have to drop from the dynamic columns the past month and add a new month to the end of the table
My solution was saving the name of the column into a local variable and then adding it to the alter statement. But this does not work , it keeps giving me a error as under:
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '@month_b'
I will now add the 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 cannot find a solution to this, can you help me.
Thank you in advance
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With