SQL Server: How to create a temp table with unkown columns dataype/names?

Question

I need to make a query on multiple databases. I got the part for building the query for every database but now i need to put the result in something for each query then i can return it.

@requete is a query

ALTER PROCEDURE [dbo].[RequeteMultiBd]
    @requete varchar(max)
AS
BEGIN

    --get server + database name
    select dbo.trim(Serveur)+'.'+ dbo.trim(db) as name, row_number() over (order by db) seq into #tmp from dbo.DataBase;

    declare @name sysname
    declare @sql nvarchar(max)
    declare @seq int
    set @seq = 0

    --getting the next name and seq
    select top 1 @name = name, @seq = seq
      from #tmp where seq > @seq order by seq

    set @sql = replace(@requete, '<bd>', @name);
    --Trying to init the table (that part doesnt work)
    insert into #result exec( @sql );

    --Filling up the table
    WHILE (1=1)
    BEGIN
     select top 1 @name = name, @seq = seq
      from #tmp where seq > @seq order by seq
     if(@@rowcount = 0) break;

     set @sql = replace(@requete, '<bd>', @name);
     insert into #result exec( @sql );

    END
    select * from #result

END

From that code i get this error because #result doesn't exit. I need to create it with dynamic column names but how ?

Invalid object name '#result'

Answer 1

Try using SELECT .... INTO #Result FROM ...

This will create the table with the correct columns.

http://www.w3schools.com/Sql/sql_select_into.asp

Answer 2

you may be able to share a temp table between procedure calls, I never did it between databases though...

check out this:

How to Share Data Between Stored Procedures by Erland Sommarskog

Answer 3

You do not need to use dynamic sql to run your stored procedure, you can call it like:

EXEC @requete '<bd>', @name

where @requete is a valid name like server.owner.procedure_name