I am very new to SQL in that I just finished reading Sams Teach Yourself SQL in 10 Minutes and that is my only SQL knowledge. So now that I'm done with the book, I'm trying to create some tables so I can play around with them. I can easily create a table with a known amount of columns and specified header. Where I am having trouble is creating a table with an unknown amount of columns and a date as the header. What I have tried so far is this:
DECLARE @start_date AS DATE
DECLARE @end_date AS DATE
DECLARE @curr_date AS DATE
DECLARE @column_name AS CHAR(10)
SET @start_date = 2016-01-02
SET @end_date = 2016-12-31
SET @curr_date = @start_date
WHILE @curr_date < @end_date
SET @curr_date = DATEADD(DD, 7, @curr_date)
ALTER TABLE Project_1
ADD @curr_date DOUBLE
What I tried to do here is make a start and end point for the loop and use the loop condition which is stored in a local variable as my column header since that is what I need the column to be titled. I also tried using CAST to cast it as a char but the DBMS is delighted to let me know that there is Incorrect syntax near '@curr_date' on that last line (the ADD line) because it doesn't like that I'm trying to name a column with a local variable (I think). I don't have a sample output but the output should be a table with the first column defined as CHAR and titled emp_name because it will be holding names. All other columns defined as type DOUBLE and should be NULL because they will be holding a number of hours and must have the current date as the header @curr_date. I think all columns added to a table through the ALTER method are defaulted to NULL anyways. I've seen examples of dynamic SQL where you declare a variable to hold the select statement but I don't really understand how they add columns to a table. I'm not sure if this can be done in the CREATE statement but if it can that would be great to see. Also, this needs to be variable in the fact that I could change the @end_date to lets say... they year 2046.
Security is not an issue here
I agree with all of the comments about using ROWs not dynamically adding columns you can always dynamically pivot those latter and it will have more flexibily. So maybe some schema considerations but just to answer your specific question you where close.....
DECLARE @start_date AS DATE
DECLARE @end_date AS DATE
DECLARE @curr_date AS DATE
DECLARE @column_name AS CHAR(10)
SET @start_date = '2016-01-02'
SET @end_date = '2016-12-31'
SET @curr_date = @start_date
WHILE @curr_date < @end_date
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @curr_date = DATEADD(DD, 7, @curr_date)
SET @SQL = 'ALTER TABLE TableB
ADD [' + CAST(@curr_date AS VARCHAR(10)) + '] FLOAT'
--PRINT @SQL
EXECUTE (@SQL)
END