Assign year variable to column alias for excel sheet

Question

I have an excel sheet which is bound to a stored procedure. In the stored procedure I am selecting the columns that appear in the excel sheet. Now I'm facing an issue when I wanted to add some more columns:

• Some value 2016
• Some value 2017
• Some value 2018

The first column is adding the actual year to the header and the two others the next one and the year after the next one.

My problem is that I don't know how to do this dynamically. I've tried something like this:

DECLARE @actualYear INT = YEAR(GETDATE())

SELECT tab.Name,
    myTable.SomeValue [Some value @actualYear],
    myTableNext.SomeValue [Some value @actualYear+1],
    myTableAfterTheNext.SomeValue [Some value @actualYear+2]
FROM SomeTable tab
LEFT JOIN MyTable myTable ON tab.SomeId = myTable.SomeId
    AND myTable.[Year] = @actualYear
LEFT JOIN MyTable myTableNext ON tab.SomeId = myTableNext.SomeId
    AND myTable.[Year] = (@actualYear+1)
LEFT JOIN MyTable myTableAfterTheNext ON tab.SomeId = myTableAfterTheNext.SomeId
    AND myTable.[Year] = (@actualYear+2)

The output is the following:

+------+------------------------+--------------------------+--------------------------+
| Name | Some value @actualYear | Some value @actualYear+1 | Some value @actualYear+2 |
+------+------------------------+--------------------------+--------------------------+

---

Second try:

SELECT tab.Name,
    myTable.SomeValue ['Some value' + @actualYear]
    ...

Output:

+------+----------------------------+ ...
| Name | 'Some value' + @actualYear | ...
+------+----------------------------+ ...

---

How can I get the correct column headers dynamically?

Answer 1

You'll have to create a dynamic sql query like so (short example):

declare @i int;
declare @sql nvarchar(max);

set @i = 2016;
set @sql = N'select 1 as [' + cast(@i as nvarchar) + N']';

exec(@sql);

Translated to your sql query this should be something like this:

declare @sql nvarchar(max);
declare @actualYear int = year(getdate());

set @sql = @sql + N'select  tab.Name, '
set @sql = @sql + N'        myTable.SomeValue [' + cast(@actualYear as nvarchar) + N'], '
set @sql = @sql + N'        myTableNext.SomeValue [' + cast(@actualYear + 1 as nvarchar) + N'], '
set @sql = @sql + N'        myTableAfterTheNext.SomeValue [' + cast(@actualYear + 2 as nvarchar) + N'] '
set @sql = @sql + N'from    SomeTable tab '
set @sql = @sql + N'left join MyTable myTable '
set @sql = @sql + N'on      tab.SomeId = myTable.SomeId '
set @sql = @sql + N'        and myTable.Year = @actualYear '
set @sql = @sql + N'left join MyTable myTableNext '
set @sql = @sql + N'on      tab.SomeId = myTableNext.SomeId '
set @sql = @sql + N'        and myTable.Year = (@actualYear + 1) '
set @sql = @sql + N'left join MyTable myTableAfterTheNext '
set @sql = @sql + N'on      tab.SomeId = myTableAfterTheNext.SomeId '
set @sql = @sql + N'        and myTable.Year = (@actualYear + 2); '

exec(@sql);

How to easily convert an SQL query into a dynamic SQL query:

Note, within Notepad++ you should replace the regular expression ^(.*)$ with set @sql = @sql + N'\1 '.

Update

Possible implementation of the above into a stored procedure (short example only):

IF OBJECT_ID('procTest', 'P') IS NOT NULL
    DROP PROCEDURE procTest;
GO

CREATE PROCEDURE procTest
AS
BEGIN
    DECLARE @i INT;
    DECLARE @sql NVARCHAR(MAX);

    SET @i = 2016;
    SET @sql
        = N'insert into #t (Column1) VALUES (' + CAST(@i AS NVARCHAR)
          + N'); ' + N'insert into #t (Column1) '
          + N'SELECT cast(1 as nvarchar) as [' + CAST(@i AS NVARCHAR) + N']';

    EXEC (@sql);
END;
GO

CREATE TABLE #t
(
    Column1 NVARCHAR(MAX)
);

EXEC dbo.procTest;

SELECT *
FROM #t;

DROP TABLE #t;