SQL Server stored procedure create table with dynamic number of column

Question

I'm new to stored procedure, and trying to figure out how to create a table with a dynamic number of columns.

I'm using Microsoft SQL Server 2008.

I have the following query which is meant to be used to store data passed from a XML source into a table which should be created by this stored procedure.

CREATE PROCEDURE AddClaimData_newV2
    @xml_text VARCHAR(4000),
    @devYearColumnNumber INT
AS

  DECLARE @i INT
  DECLARE @tempTable TABLE (
       ProductName       VARCHAR(50), 
       Year              INT, 
       Value1            FLOAT , 
       Value2            FLOAT , 
       Value3            FLOAT , 
       Value4            FLOAT )

  EXEC sp_xml_preparedocument @i OUTPUT, @xml_text 

  INSERT INTO @tempTable
    SELECT * FROM 
      OPENXML(@i, '/root/Product/Year/Value1/Value2/Value3/Value4', 1) 
      WITH 
        (ProductName    VARCHAR(50) '../../../../../@Name', 
         Year     INT  '../../../../@Year', 
         Value1   FLOAT  '../../../@Value', 
         Value2   FLOAT  '../../@Value',
         Value3   FLOAT  '../@Value',
         Value4   FLOAT  '@Value')

/* create a new table and store all the data from @tempTable */

EXEC sp_xml_removedocument @i

Basically, ProductName and Year are fixed columns, but the Value[i] columns are decided by parameter devYearColumnNumber.

My questions are :

1. how to use the parameter to dynamically create those Value[i] columns
2. then, how to create a new table with those columns to store data from @tempTable

Answer 1

declare @currentColumnNumber int, @sqlCommandToCreateTable nvarchar(4000)
set @currentColumnNumber = 1
set @sqlCommandToCreateTable = 'CREATE TABLE testClaimData (
        ProductName       VARCHAR(50),
        Year              INT '
while @currentColumnNumber <= @devYearColumnNumber
begin
    set @sqlCommandToCreateTable = @sqlCommandToCreateTable + ' , 
        Value' + convert(varchar, @currentColumnNumber) + '            FLOAT'
    set @currentColumnNumber = @currentColumnNumber + 1
end
set @sqlCommandToCreateTable = @sqlCommandToCreateTable + ' )'
exec sp_executeSql @sqlCommandToCreateTable

--[Test this with:]-- exec sp_executeSql N'select * from testClaimData'

--similar mechanism for the "INSERT INTO" here