SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO
Traditional Method:
/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSPGO
/* SELECT from TempTable */
SELECT *FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO
Alternate Method: Table Valued Function
/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,IntValue INT
)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue
INTINSERT INTO @retTestFnSELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *FROM dbo.TestFn()
GO