sp_executesql and table output

SQL Server 2005 allows to use INSERT INTO EXEC operation (https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sqlallproducts-allversions).

You might create a table valued variable and insert result of stored procedure into this table:

DECLARE @tempTable table(myParam1 int, myParam2 int);
DECLARE @statement nvarchar(max) = 'SELECT 1,2';
INSERT INTO @tempTable EXEC sp_executesql @statement;
SELECT * FROM @tempTable;

Result:

myParam1    myParam2
----------- -----------
1           2

or you can use any other your own stored procedure:

DECLARE @tempTable table(myParam1 int, myParam2 int);
INSERT INTO @tempTable EXEC [dbo].[my_procedure];
SELECT * FROM @tempTable;

Resolved, thanks to all for tips:

DECLARE @DBName varchar(255) 
DECLARE @q varchar(max) 
CREATE table #tempTable(myParam1 int, -- other params)

SET @DBName = 'my_db_name'
SET @q = 'insert into #tempTable exec ['+@DBName+'].[dbo].[my_procedure]'
EXEC(@q)

SELECT * FROM #tempTable
drop table #tempTable

@tempTable's scope is limited to the current procedure.

You could replace the @tempTable with a global temporary table (i.e. ## table), but be very careful with the scope of that table and be sure to drop it when the procedure ends