Cannot get results of a stored procedure into a #TempTable to work

Question

I am using SQL Server 2008 R2 and am trying to get the results of a stored procedure into a temporary table that I can access later on in the calling stored proc. My TSQL is as follows:

CREATE PROCEDURE sp_ToBeCalled AS
(
   @SomeParam INT
)
BEGIN
    SELECT * FROM tblSomeTable WHERE SomeField = @SomeParam 
END


CREATE PROCEDURE sp_CallingProcedure AS
(
   @SomeOtherParam INT
)
BEGIN
    -- A
    SELECT * INTO #MyTempTable FROM sp_ToBeCalled(@SomeOtherParam)

    -- B
    SELECT * FROM #MyTempTable FOR XML RAW
END

This all compiles fine however when I call sp_CallingProcedure statement -- B returns an error that #MyTempTable.

How can I do "A" so that I can access its results from within a #MyTempTable table without having to declare the structure of #MyTempTable first?

I am looking for a solution that I can use generically. I have a number of existing stored procedures that I need to call from various callers where getting the results queryable is a necessity. I cannot change the existing stored procedures.

I don't want to use

1. OPENQUOERY() - requires a custom linked server definition
2. sp_ExecSql() - means I have to build up dynamic SQL which does not give me SP compile time checking.

Answer 1

You are trying to use a Procedure like a tabular function.

Try using

INSERT INTO #MyTempTable (column1, column2...)
exec sp_ToBeCalled(@SomeOtherParam)

Answer 2

A great reference: http://www.sommarskog.se/share_data.html

I managed to partially solve my issue by doing the following:

1) Custom Stored Procedure to select a ROWSET into a global temp table 2) Calling SP calls 1) and then transfers the ##GlobalTempTable into a local #TempTable for processing

This works but has the following "issues":

• Potential security risk as "Adhoc Distributed Queries" functionality needs to be turned on
• Still requires a Global Temp table that needs to be cleaned up by the caller. Temp table naming is also problematic as multiple 2) will cause an issue.

I include my code below in case it helps someone else. If anyone is able to improve on it please feel free to post.

/* This requires Adhoc Distributed Queries to be turned on:
   sp_configure 'Show Advanced Options', 1
   GO
   RECONFIGURE
   GO
   sp_configure 'Ad Hoc Distributed Queries', 1
   GO
   RECONFIGURE
   GO
*/

-- Adapted from: http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
)
AS 
BEGIN
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
        ';trusted_connection=yes;Database=' + DB_NAME() + ''''

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''



    SET @sql = '
SELECT
    *
INTO 
    ' + @tableName + ' 
FROM
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)
END
GO

and then to use in another SP as follows:

EXEC ExecIntoTable '##MyGlobalTable', 'sp_MyStoredProc 13, 1'
SELECT * 
INTO #MyLocalTable
FROM ##MyGlobalTable
DROP TABLE ##MyGlobalTable

SELECT * FROM #MyLocalTable