How to return temporary table from stored procedure

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to return temporary table from stored procedure

Examples related to sql-server

Examples related to stored-procedures

Examples related to temp-tables