How do I CREATE TABLE with dynamic column names (saved in variables)?

Question

I want to create a table with column names like Members_2014 and Spend_2014. I've tried to have the year setup as a variable and concatenate Member_ + @year, so that the @year will decrease, but the code I've written doesn't seem to work:

declare @li_year    int
declare @li_year2   int
declare @li_year3   int
declare @li_year4   int
declare @li_year5   int

set @li_year = datepart(year, GETDATE())
set @li_year2 = @li_year-1
set @li_year3 = @li_year-2
set @li_year4 = @li_year-3
set @li_year5 = @li_year-4

drop table [scratchdb].[dbo].results_test
create table [scratchdb].[dbo].results_test (RDesc varchar(30), 
   'Members_' + @li_year int, 
   'Spend_' + @li_year money,
   'Members_' + @li_year2  int, 
   'Spend_' + @li_year2 money,
   'Members_' + @li_year3  int, 
   'Spend_' + @li_year3 money,
   'Members_' + @li_year4  int, 
   'Spend_' + @li_year4 money,
   'Members_' + @li_year5 int, 
   'Spend_' + @li_year5 money)

Answer 1

I'd like to start with a personal observation: This is quite likely a bad idea in the long run. Ideally, database schemas are fixed. Your script, however, would produce a different database schema depending on the year you're running it it (if it worked, that is). Which means, for example:

• You won't be able to use your script to re-create a schema that used to work one, two years earlier.
• Your will have to rewrite all queries and applications that run against this database every year.

So in the short run, what you're doing might be OK, but it would be better to simply normalize your schema and make the year an additional column:

CREATE TABLE dbo.results_test
(
    RDesc   VARCHAR(30),
    Year    SMALLINT NOT NULL UNIQUE, -- UNIQUE to enforce at most one record per year
    Members INT,
    Spend   MONEY
);

If you decide against this and in favour of your current solution, then you need to put the whole SQL statement in a string variable, not just parts of it:

DECLARE @sql NVARCHAR(MAX) =
     'CREATE TABLE … Members_' + CAST(@li_year AS NVARCHAR) + ' INT, …' + …
--                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^       ^^^^
--                              regular string concatenation

EXECUTE sp_executesql @sql;

SQL statements contained in a string variable are called Dynamic SQL, and the sp_executesql system stored procedure helps you run such a statement.