mercredi 27 juin 2018

How do I use a macrovariable in an if or case statement

I have a while loop that runs over several years of data. During this time one attribute in some tables I need to join to has changed its name. To get around this I thought I could use a case or if statement. But I'm unable to get the Boolean expression to understand or accept the value in my macrovariable.

Very simplified (with a very short period just for example and all the other code in the while loop is working) my code looks like this:

declare @StartYear smallint = 2010
declare @Year smallint = @StartYear
declare @EndYear smallint = 2013
declare @YearVC varchar(4) = ''
declare @CreateTable varchar(MAX) = ''

while @Year <= @EndYear
  begin

   set @YearVC = cast(@Year as varchar)

   set @CreateTable = '
     if ' + @Year + ' < 2011
       select t1.*, t2.a, t2.b, t2.c as e /*Variable t2.e change it´s origin name over time.*/
       into ResultTable_' + @YearVC + '
       from TableOne_' + @YearVC + ' as t1 left join TableTwo_' + @YearVC + ' as t2 on t1.a=t2.a

     if ' + @Year + ' >= 2011
       select t1.*, t2.a, t2.b, t2.d as e /*Variable t2.e change it´s origin name over time.*/
       into ResultTable_' + @YearVC + '
       from TableOne_' + @YearVC + ' as t1 left join TableTwo_' + @YearVC + '  as t2 on t1.a=t2.a
     '

     exec (@CreateTable)

set @Year = @Year + 1

end

I've also tried with a case statement (all the variable declarations, the while-loop and the @CreateTAble statement is the same so I just write my select syntax below):

select t1.*, t2.b,
  case
    when ' + @Year + ' < 2011 then t2.c
    else t2.d
  end as e
into ResultTable_' + @YearVC + '
from TableOne_' + @YearVC + ' as t1 left join TableTwo_' + @YearVC + ' as t2 on t1.a=t2.a

Both these examples give me a Msg 245 error:

Conversion failed when converting the varchar value '

with references to my Boolean expressions?

I've made certain in several and different ways that the macrovariable value that I send to that expression is not a varchar. When I try with a varchar variable I get a Msg 207 error instead:

Invalid column name 'd'.

This means that it can't find the attribute name for the attribute which will be valid from 2011 and later.

What am I missing here? How should I define my "if" or "case" statement so that SQL-server will understand that for the years before 2011 it should look for the attribute c and for the years from 2011 and further it should look for attribute d?

I use MS SQL-server version 14.

Aucun commentaire:

Enregistrer un commentaire