Result Set clause with the EXECUTE statement, which lets you redefine the name and data types of the columns being returned from the stored procedure. This comes in very handy when you want to insert the records coming in a result set to a temporary table with a specific column name and data type and you don’t want to rely on what is being returned by the stored procedure.
The WITH RESULT SETS clause can also be used with a stored procedure, which returns multiple result sets and for each result set you can define the column name and data types for each column separately.
Please note:
1.Sometimes if you want to restrict a stored procedure to return a result set you can use the RESULT SETS NONE clause.
2.The WITH RESULT SETS option cannot be specified in an INSERT…EXEC statement.
3.The number of columns being returned as part of result set cannot be changed.
CREATE PROCEDURE SampleSP
AS
SELECT 1 AS Col1, 2 AS Col2
UNION
SELECT 11, 22
GO
CREATE TABLE #TempTable (Col1 INT, Col2 INT)
GO
INSERT INTO #TempTable
EXEC SampleSP
GO
SELECT *
FROM #TempTable
GO
