Giving a presentation on using SSIS to handle Slowly Changin Dimensions tomorrow night at the Space Coast SQL User Group.  Goto http://spacecoast.sqlpass.org for details.  If you can't make it, then try and catch me at SQL Saturday on the 17th - http://www.sqlsaturday.com/eventhome.aspx?eventid=32

The run this Thursday will be the dry run so come to see me bumble through the presentation.  Come the Saturday to see the polished version.

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held October 17, 2009 at Seminole Community College, 100 Weldon Boulevard, Sanford, FL 32773. Admittance to this event is free, but to to the challenging nature of fund raising this year there will be a $10 fee due at registration to cover the cost of lunch (a great boxed lunch from Jason's Deli). All other costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleages know about the event.

More Info: http://www.sqlsaturday.com/eventhome.aspx?eventid=32

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
                
              
              

            
          You can then call this function to get what your results a such:

            
              
                

                  
                    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.