Menu
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???
803
No, you cannot "return" a temp table - you can create that temp table before calling your function, and have your function write data into that temp table. But this has a tendency to get rather messy .... you need to make sure to have the temp table created before calling the function.....
The RETURN exits the stored procedure, and nothing that follows it will be executed, including the SELECT statement on the following line. Otherwise, if you want the data for the entire table, as your question shows, add a SELECT after the INSERT . But don't put RETURN in front of it!
When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.
In similar way, you can store stored procedure output into temporary/ temp table as shown below. CREATE TABLE #StudentData_Log (ID INT, Name VARCHAR(100)) SELECT * FROM #StudentData_Log; Lets execute the stored procedure and insert output into above temp table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
