possible to insert the 1st of n tables from a proc into a temp table?

Question

I think the answer to this is no going in, but I could use a sanity check.

I have a proc that returns 3 tables. Table1 has 23 columns. Table2 has 12 columns. Table3 has 2 columns. I need to run this proc and dump the results of Table1 into a temp table. I created a @table and then did this:

insert @myTable 
exec [myDb].dbo.[multitableProc] 'A', 'B', 'C', 100

But when I do this the error is:

Insert Error: Column name or number of supplied values does not match table definition.

Which makes me think SQL is pretty damn confused about what I want to do. I'm positive the table has the correct number of columns, double checked that. The datatypes are correct.

In conclusion, I'm pretty sure this just isn't possible, but someone here might have a trick. Changing the source proc is not allowed. I did see one similar question, but he was trying to do some union type stuff, not the same.

Answer 1

The only way I can think of doing it is with a CLR stored procedure. Have the CLR sproc call the sproc that returns the three tables. Then, grab only the single table you want and return that from your CLR sproc. Insert the results of your CLR sproc into your @myTable. I think this will provide you with the extra level of indirection necessary to get rid of the column mismatch from the other two result sets.

Okay, I created a greatly simplified version to test. I returned two result sets from a table, one with one column and one with two ...

DECLARE @myTable TABLE ( col1 varchar(50) )

INSERT @myTable( col1 )
EXEC TestTable

Gets the correct result ...

Msg 213, Level 16, State 7, Procedure TestTable, Line 23 Column name or number of supplied values does not match table definition.

I made this CLR sproc that only takes the first result set (the one with only one column) from my TestTable TSQL sproc and returns only that table ...

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void FilterResult()
    {
        using (var conn = new SqlConnection("context connection = true"))
        {
            var retVal = new SqlDataRecord( new SqlMetaData("col1",
                                            SqlDbType.NVarChar, 50));
            conn.Open();

            var cmd = new SqlCommand("exec TestTable", conn);

            SqlContext.Pipe.SendResultsStart(retVal);

            using (var dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    var tmpCol = dr.GetString(0);
                    retVal.SetString(0, tmpCol);

                    SqlContext.Pipe.SendResultsRow(retVal);
                }

                dr.Close();
            }

            SqlContext.Pipe.SendResultsEnd();
        }
    }
};

Then, I am able to execute ...

INSERT @myTable( col1 )
EXEC TestFilter  -- note the CLR sproc here instead of the TSQL sproc

... and all is happy ...

(2 row(s) affected)

At the end of the day, it might be more trouble than it's worth, but it does work as a proof of concept. (No warranty as to performance tho.)