如何从存储过程中返回临时表

Question

CREATE PROCEDURE [test].[proc]
@ConfiguredContentId int,
@NumberOfGames int
AS
BEGIN
 SET NOCOUNT ON
 RETURN 
 @WunNumbers TABLE (WinNumb int)

    INSERT INTO @WunNumbers (WinNumb)
 SELECT TOP (@NumberOfGames) WinningNumber
 FROM [Game].[Game] g
 JOIN [Game].[RouletteResult] AS rr ON g.[Id] = rr.[gameId]
 WHERE g.[ConfiguredContentId] = @ConfiguredContentId
 ORDER BY g.[Stoptime] DESC

 SELECT WinNumb, COUNT (WinNumb) AS "Count"
 FROM @WunNumbers wn
 GROUP BY wn.[WinNumb]
END
GO

这个存储过程返回了第一个select语句的值，但我想要返回第二个select语句的值。表@WunNumbers是一个临时表。

有什么想法吗？

- dani

---

请重新格式化您的 SQL 代码。 - Anwar Chandra

那段代码不是有效的SQL。如果“RETURN @WinNumbers”改为“DECLARE @WinNumbers”，它可能是有效的，但是其余部分看起来正确，可以返回最终结果集。 - Damien_The_Unbeliever

我现在明白了，我发布了错误的代码。虽然有“DECLARE @WinNumbers”，但它仍然不能正常工作。 - dani

Answer 1

看一下这段代码：

CREATE PROCEDURE Test

AS
    DECLARE @tab table (no int, name varchar(30))

    insert @tab  select eno,ename from emp  

    select * from @tab
RETURN

- KV Prajapati

---

这个函数如何将一个表变量返回给调用者？它目前只返回一个结果集。 - John Sansom

它返回选择语句（实际问题）的结果，本例中为本地表变量@tab的内容。 - JeffO

如果我执行 EXECUTE Test，我如何访问 @tab 变量？ - whytheq

我正在做类似的事情；当我在管理工具中运行它时，我可以看到我的结果集，但是当我使用ADO.net运行它时，SqlReader获取了一个空的结果集。 - Isaac Kleinman

Answer 2

你正在使用哪个版本的SQL Server？在SQL Server 2008中，你可以使用表参数和表类型。

另一种方法是从用户定义的函数返回一个表变量，但我不太喜欢这种方法。

你可以在这里找到一个例子。

- John Sansom

Answer 3

可以在调用者中创建临时表，然后从被调用的存储过程中填充数据。

  create table #GetValuesOutputTable(
     ...   
  );

  exec GetValues; -- populates #GetValuesOutputTable

  select * from #GetValuesOutputTable;

这种方法相对于“插入执行”有一些优点，它可以嵌套并且可以用作输入或输出。

缺点是“参数”不公开，表的创建存在于每个调用者中，并且表的名称可能与其他临时对象冲突。当临时表的名称与存储过程名称紧密匹配并遵循某些约定时，这有助于解决问题。

更进一步地说，对于仅输出的临时表，被调用的存储过程可以同时支持插入执行方法和临时表方法。这对于链接存储过程没有太大帮助，因为表仍然需要在调用者中定义，但可以帮助简化从cmd行测试或外部调用。

  -- The "called" SP
  declare
      @returnAsSelect bit = 0;

  if object_id('tempdb..#GetValuesOutputTable') is null
  begin
      set @returnAsSelect = 1;
      create table #GetValuesOutputTable(
         ...   
      );
  end

  -- populate the table

  if @returnAsSelect = 1
      select * from #GetValuesOutputTable;

- crokusek

Answer 4

是的，你可以。

在你的存储过程中，你填充表格@tbRetour。

在你的存储过程的最后，你写道：

SELECT * FROM @tbRetour 

要执行存储过程，您可以编写如下语句：

USE [...]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[getEnregistrementWithDetails]
@id_enregistrement_entete = '(guid)'

GO

- André Millaire

Answer 5

首先创建一个真正的、永久的表格作为模板，该表格具有返回临时表所需的布局，使用标识它为模板并将其符号链接到SP的命名约定，例如tmp_SPName_Output。这个表格永远不会包含任何数据。

在SP中，使用INSERT将数据加载到遵循相同命名约定的临时表中，例如#SPName_Output，假定该表已经存在。您可以测试它是否存在，并在不存在时返回错误。

在调用SP之前，使用这个简单的select语句创建临时表：

SELECT TOP(0) * INTO #SPName_Output FROM tmp_SPName_Output;
EXEC SPName;
-- Now process records in #SPName_Output;

这样做有以下几个明显的优点：

• 临时表是局部于当前会话的，不像 ## 那样会与来自不同会话的并发调用 SP 冲突。当超出范围时，它也会被自动删除。
• 模板表与 SP 一起维护，因此如果对输出进行更改（例如添加新列），则 SP 的预先存在的调用者不会中断。调用者无需更改。
• 您可以为一个 SP 定义任意数量的具有不同命名的输出表，并将它们全部填充。您还可以定义具有不同命名的替代输出，并让 SP 检查临时表的存在情况以确定需要填充哪些表。
• 同样，如果进行了重大更改但仍想保持向后兼容性，则可以使用新的模板表和命名来支持后续版本，但仍通过检查调用者创建的临时表来支持早期版本。

- JohnRC

Answer 6

一个过程的返回类型是int。

你也可以返回结果集（就像你当前的代码所做的那样）（好吧，你也可以发送字符串消息）

这些是你唯一能够做出的“返回”。虽然你可以向过程添加表值参数（参见BOL），但它们只能作为输入。

编辑：

（或者正如另一个帖子提到的那样，你也可以使用表值函数而不是过程）

- Damien_The_Unbeliever