SQL Server存储过程创建具有动态列数的表

Question

我是存储过程的新手，并试图弄清楚如何创建具有动态列数的表。

我正在使用 Microsoft SQL Server 2008。

我有以下查询，该查询旨在用于将从 XML 源传递的数据存储到应由该存储过程创建的表中。

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

基本上，ProductName 和 Year 是固定列，但 Value[i] 列由参数 devYearColumnNumber 决定。

我的问题是：

1. 如何使用参数动态创建这些 Value[i] 列
2. ，然后如何使用这些列创建一个新表来存储来自 @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