如何在不修改SP的情况下将附加列添加到SP返回的结果集中？

I have a Stored Procedure (SP), named myStoredProcedure, returning me such output based on startDate and endDate user-defined parameters:

我有一个名为myStoredProcedure的存储过程（SP），根据startDate和endDate用户定义的参数返回这样的输出：

PrimaryName  SecondaryName  Volume
    A             B           20
    C             D           30
    A             D           50
    ...

So, Volume represents the sum of all the cases between the dates defined.

因此，Volume表示所定义日期之间所有情况的总和。

In another SP, named mySecondStoredProcedure, I am using the first SP to get the result there. However, my problem is that I need an additional attribute in my output, which is year, I want to see year based volumes. Therefore, the output I would like to see is something like that

在另一个名为mySecondStoredProcedure的SP中，我使用第一个SP来获得结果。但是，我的问题是我的输出需要一个额外的属性，也就是今年，我希望看到基于年份的卷。因此，我希望看到的输出是这样的

assume startDate: 2014, endDate: 2015:

假设startDate：2014，endDate：2015：

PrimaryName  SecondaryName  Volume   Year
    A             B           12      2014
    C             D           14      2014
    A             D           20      2014
    A             B           8       2015
    C             D           16      2015
    A             D           30      2015
    ...

I am not allowed to modify myStoredProcedure. Therefore I build a while loop in the second SP to receive it. My code is like:

我不允许修改myStoredProcedure。因此，我在第二个SP中构建了一个while循环来接收它。我的代码是这样的：

declare @temp_table table
(
  PrimaryGroup varchar(10),
  SecondaryGroup varchar(10),
  Volume int
)

while @startDate < @endDate
begin
   insert into @temp_table
   exec myStoredProcedure @startDate @endDate

   set @startDate = DATEADD(YEAR,1,@startDate)
end

select * from @temp_table

This is giving me the result without the year column. I need a year column like I showed in my example output above. I could not find a way to add it. There is no primary key in the result set returned by myStoredProcedure. Also, SQL Server 2008 does not let me add a year column in @temp_table, saying that fields are not matching. How can I add the year column properly? Any help would be appreciated!

这给了我没有年份列的结果。我需要一个年份列，就像我在上面的示例输出中所示。我找不到添加它的方法。 myStoredProcedure返回的结果集中没有主键。此外，SQL Server 2008不允许我在@temp_table中添加年份列，表示字段不匹配。如何正确添加年份列？任何帮助，将不胜感激！

EDIT: When I add year column in the definition of @temp_table, the error I receive: Column name or number of supplied values does not match table definition.

编辑：当我在@temp_table的定义中添加年份列时，我收到的错误：列名或提供的值的数量与表定义不匹配。

3 个解决方案

#1

You're close with the syntax you currently have, you'll just need to add the year to the temp table and supply it after calling the stored procedure. In addition, you will also need to specify the columns being inserted (a practice well worth getting in the habit of) as your procedure doesn't return the same number of columns.

您已经接近了当前的语法，您只需要将年份添加到临时表中并在调用存储过程后提供它。此外，您还需要指定要插入的列（这是一种值得习惯的习惯），因为您的过程不会返回相同数量的列。

declare @temp_table table
(
  PrimaryGroup varchar(10),
  SecondaryGroup varchar(10),
  Volume int,
  Year int
)

while @startDate < @endDate
begin
   insert into @temp_table (PrimaryGroup, SecondaryGroup, Volume)
   exec myStoredProcedure @startDate @endDate

   Update   @temp_table
   Set      Year = @StartDate
   Where    Year Is Null

   set @startDate = DATEADD(YEAR,1,@startDate)
end

select * from @temp_table

#2

Add a Year column to your temp table, and apply the structured insert

将Year列添加到临时表，并应用结构化插入

declare @temp_table table
(
  PrimaryGroup varchar(10),
  SecondaryGroup varchar(10),
  Volume int,
  Year  int
)

while @startDate < @endDate
begin
   insert into @temp_table (PrimaryName,SecondaryName,Volume)
   exec myStoredProcedure @startDate @endDate

   Update @temp_table set Year = @startDate where Year is Null

   set @startDate = DATEADD(YEAR,1,@startDate)
end

select * from @temp

#3

Create a second table variable that will hold the result:

创建一个将保存结果的第二个表变量：

declare @result_table table
(
  Year int,
  PrimaryGroup varchar(10),
  SecondaryGroup varchar(10),
  Volume int
)

Then in the while loop after fetching the result into @temp_table:

然后在将结果提取到@temp_table之后的while循环中：

insert into @result_table
    select <year>, PrimaryGroup, SecondaryGroup, Volume from @temp_table;

truncate @temp_table;