This is impossible. Don't try. There are a few 'hacky' ways to do this, but why? It can be doneif you slightly modify the requirement. If you can abandon the use of a stored procedure for a User Defined Function, you can use an Inline Table-Value User-Defined Function. This is essentially a stored proc (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement. Here's a good quick article on it and other user defiend functions. If you still have a driving need for a Stored Procedure, you can wrap the Inline Table-Value User-Defined Function with a stored procedure. The stored proc just passes parameters when it calls select * from the Inline Table-Value User-Defined Function.
So for instance, you'd have a Inline Table-Value User-Defined Function to get a list of customers for a particular region:
CREATE FUNCTION ufCustomersByRegion (@RegionID int)
RETURNS TABLE
AS
RETURN SELECT * FROM customers WHERE RegionID = @RegionIDGO
SELECT * FROM CustomersbyRegion(1)
Or to do a SELECT INTO:
SELECT * INTO CustList FROM CustomersbyRegion(1)
If you still need a stored proc, then wrap the function as such:
CREATE PROCEDURE uspCustomersByRegion
@regionID int
AS
BEGIN
SELECT * FROM CustomersbyRegion(@regionID);
END
GO
I think this is the most 'hack-less' method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the Inline Table-Value User-Defined Function in the stored proc, you have access to the functionality in two ways. Plus! You have only one point of maintenance for the actual SQL code.