Temp table in SQL

	Create Table #TEMPDIAG (ID INT, NAME VARCHAR(255))
	declare @lquery varchar(5000)
	set @lquery='Select id,name from sysobjects'
	INSERT INTO #TEMPDIAG
	EXEC (@lQuery)
	select *from #TEMPDIAG
	Drop Table #TEMPDIAG
	==========================================
	/* 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
	/* Create TempTable */
	CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
	GO
	/* Run SP and Insert Value in TempTable */
	INSERT INTO #tempTable (MyDate, IntValue)
	EXEC TestSP
	GO
	/* SELECT from TempTable */
	SELECT *
	FROM #tempTable
	GO
	/* Clean up */
	DROP TABLE #tempTable
	GO
	====================================
	/* Create table valued function*/
	CREATE FUNCTION dbo.TestFn()
	RETURNS @retTestFn TABLE
	(
	MyDate SMALLDATETIME,
	IntValue INT
	)
	AS
	BEGIN
	DECLARE @MyDate SMALLDATETIME
	DECLARE @IntValue INT
	INSERT INTO @retTestFn
	SELECT 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

view raw gistfile1.sql hosted with ❤ by GitHub