CODEWITHSUNDEEP
sql-serverstored-procedurestemp-tables
dani
dani

Reputation: 469

How to return temporary table from stored procedure

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

This stored procedure returns values from first select statement, but I would like to have values from second select statement to be returned. Table @WunNumbers is a temporary table.

Any ideas???

Upvotes: 16

Views: 173184

Answers (6)

JohnRC
JohnRC

Reputation: 1371

First create a real, permanent table as a template that has the required layout for the returned temporary table, using a naming convention that identifies it as a template and links it symbolically to the SP, eg tmp_SPName_Output. This table will never contain any data.

In the SP, use INSERT to load data into a temp table following the same naming convention, e.g. #SPName_Output which is assumed to exist. You can test for its existence and return an error if it does not.

Before calling the sp use this simple select to create the temp table:

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

This has these distinct advantages:

  • The temp table is local to the current session, unlike ##, so will not clash with concurrent calls to the SP from different sessions. It is also dropped automatically when out of scope.
  • The template table is maintained alongside the SP, so if changes are made to the output (new columns added, for example) then pre-existing callers of the SP do not break. The caller does not need to be changed.
  • You can define any number of output tables with different naming for one SP and fill them all. You can also define alternative outputs with different naming and have the SP check the existence of the temp tables to see which need to be filled.
  • Similarly, if major changes are made but you want to keep backwards compatibility, you can have a new template table and naming for the later version but still support the earlier version by checking which temp table the caller has created.

Upvotes: 0

André Millaire
André Millaire

Reputation: 11

YES YOU CAN.

In your stored procedure, you fill the table @tbRetour.

At the very end of your stored procedure, you write: 

SELECT * FROM @tbRetour

To execute the stored procedure, you write:

USE [...]
GO

DECLARE @return_value int

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

GO

Upvotes: 1

crokusek
crokusek

Reputation: 5624

A temp table can be created in the caller and then populated from the called SP. 

  create table #GetValuesOutputTable(
     ...   
  );

  exec GetValues; -- populates #GetValuesOutputTable

  select * from #GetValuesOutputTable;

Some advantages of this approach over the "insert exec" is that it can be nested and that it can be used as input or output.

Some disadvantages are that the "argument" is not public, the table creation exists within each caller, and that the name of the table could collide with other temp objects. It helps when the temp table name closely matches the SP name and follows some convention.

Taking it a bit farther, for output only temp tables, the insert-exec approach and the temp table approach can be supported simultaneously by the called SP. This doesn't help too much for chaining SP's because the table still need to be defined in the caller but can help to simplify testing from the cmd line or when calling externally.

  -- 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;

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

The return type of a procedure is int.

You can also return result sets (as your code currently does) (okay, you can also send messages, which are strings)

Those are the only "returns" you can make. Whilst you can add table-valued parameters to a procedure (see BOL), they're input only.

Edit:

(Or as another poster mentioned, you could also use a Table Valued Function, rather than a procedure)

Upvotes: 0

John Sansom
John Sansom

Reputation: 41819

What version of SQL Server are you using? In SQL Server 2008 you can use Table Parameters and Table Types.

An alternative approach is to return a table variable from a user defined function but I am not a big fan of this method.

You can find an example here

Upvotes: 6

KV Prajapati
KV Prajapati

Reputation: 94645

Take a look at this code,

CREATE PROCEDURE Test

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

    insert @tab  select eno,ename from emp  

    select * from @tab
RETURN

Upvotes: 31

Related Questions