functional reason why stored procedures don't support INSERT/EXECUTE INTO?

Question

In SQL Server, there's no way to create a temp 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 have to know the SP layout beforehand, and have 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 the case? Maybe a limitation of SQL Server? Or is it just something that hasn't been added to the SQL spec yet, and I can hold out hope that one day they'll support it?

Answer 1

Not from a sproc, but you can use tabled values functions to do something similar.

Select * From fnMyFunction

You'd be able to insert into a # table if you desired.

Answer 2

A stored procedure can return many result sets, or none, and it can vary entirely depending upon the execution of the stored procedure.

When it is compiled it's meta-information does not describe it as having any specific expectable result set output.

I expect given those constraints, they elected not to implement this because of the lack of strong typing of what a stored procedure may return.

Answer 3

Try this out

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

If I needed such functionality I would use an inline UDF, like this:

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