Why don't stored procedures support INSERT / EXECUTE INTO?

Question

There is no way in SQL Server to create a temporary table on the fly from the results of a stored procedure, ala:

CREATE TABLE #temptable AS
EXEC spMyStoredProc

or

EXEC spMyStoredProc INTO #temptable

or something like that. Instead, you need to know the layout of the SP in advance, and you need to do something like this:

CREATE TABLE #temptable (col1 INT, col2 VARCHAR(255))

INSERT INTO #temptable
EXEC spMyStoredProc

Is there a functional reason why this is so? Maybe a limitation of SQL Server? Or is it just something that has not yet been added to the SQL specification, and I can express the hope that one day they will support it?

Answer 1

, .

, - .

, - , .

Answer 2

sproc, , - .

Select * From fnMyFunction

, #.

Answer 3

DECLARE @temptable TABLE (ID INT, NAME VARCHAR(255))

declare @query varchar(max)

set @query='Select whatever from whereever'

INSERT INTO @temptable

EXEC (@Query)

select *from @temptable

Answer 4

, UDF, :

CREATE PROCEDURE MySample
AS
SELECT a,b,c FROM dbo.MyInlineUDF(1,2,3)
GO

SELECT * INTO #t
FROM dbo.MyInlineUDF(1,2,3) WHERE 1=0

INSERT INTO #t EXEC MySample