sqldynamic-sqlmysql-variables

How to call a column dynamically in the WHERE clause in SQL


I'm trying to figure out how to call the column in the table where the column name matches the value of a declared variable. In this example, Tbl_B has columns labeled as DPS_YYYYMM such as 'DPS_201911', 'DPS_201910', 'DPS_201909'.... I need to find a way to call the correct column from Tbl_B for the given YYYYMM that matches the @Var. Can you please help?

Campaign_ID DPS_201911 DPS_201910
1 345 123
DECLARE @Var varchar(50)
SET @Var = '201911'

 SELECT ?????
 From Tbl_B
 WHERE campaign_Id = 1 
 

Solution

  • Use Dynamic sql like below

    DECLARE @Var varchar(50), @sql varchar(max)
    SET @Var = '201911'
    set @Var='DPS_'+ @Var;
    set @sql='SELECT '+@Var+'
    From Tbl_B
    WHERE campaign_Id = 1'
    EXEC (@sql)