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

Answer 1

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

Answer 2

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

Answer 3

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)